DBMS_METADATA.GET_DDL Scripts

To extract package definition from database:
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;
  • September 30, 2018 | 20 views
  • Comments