DBMS_METADATA.GET_DDL

###Pulling out the Source code/extracting the code

We have DBMS_METADATA.GET_DDL  Handy to complete the task.


set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('PACKAGE','') FROM dual;


To extract package body  definition from database:
set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','' ) FROM dual;

To extract user definition:

set pagesize 0
set long 100000
select dbms_metadata.get_ddl( 'USER', '' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', '' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', '' ) from dual;

To extract tablespace definition:

SELECT dbms_metadata.get_ddl('TABLESPACE','&&tbs_name')
FROM dual;

To extract table definition:
SELECT dbms_metadata.get_ddl('TABLE',upper('&tab_nm'),upper('&username')) txt from dual;


To extract index definition created on a table:
SELECT dbms_metadata.get_dependent_ddl('INDEX',upper('&tab_nm'),upper('&username')) txt from dual;

To extract grant given on a table:
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT',upper('&tab_nm'),upper('&username')) txt FROM  dual;

To extract SYNONYM on a given table:
SELECT dbms_metadata.get_ddl('SYNONYM',TABLE_NAME)  txt from dba_synonyms  where TABLE_OWNER in (upper('&username')) and TABLE_NAME=upper(upper('&tab_nm'));

To extract trigger definition:
SELECT dbms_metadata.get_ddl('TRIGGER',upper('&pkg_nm'),upper('&username')) txt from dual;

To extract privilege given on trigger:
SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT',upper('&pkg_nm'),upper('&username')) txt FROM  dual;

To extract function from database:
SELECT dbms_metadata.get_ddl('FUNCTION',upper('&pkg_nm'),upper('&username')) txt from dual;

To extract view from database:
SELECT dbms_metadata.get_ddl('VIEW',upper('&view_nm'),upper('&username')) txt from dual;