DBMS_METADATA.GET_DDL

Oracle introduced the usage of DBMS_METADSATA package's GET_DDL function to retrieve the DDL of a particular object type from 9i onwards. It takes two/three arguments depending on the type of object you want to retrieve the DDL for.

GENERAL USAGE

set long 300000 pages 0 lines 140

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;

The 'OWNER' argument is sometimes optional if you are connected as the same user who owns the object you are trying to retreive the DDL for. Pasted below are the statements to generate the DDL for most of the object types.

select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;

select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;

select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;

select dbms_metadata.get_ddl('DB_LINK','TEST.WORLD','PUBLIC') from dual;

select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','REPCAT$_REPCOLUMN_PK','SYSTEM') from dual;

select dbms_metadata.get_ddl('JAVA_SOURCE','java_util','ADAM') from dual

select dbms_metadata.get_ddl('JAVA_SOURCE','/6c363944_Dumper','SYS') from dual

select dbms_metadata.get_ddl('LIBRARY','UTL_SMT_LIB','SYS') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;

select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION') from dual;

select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE','XMLPARSER') from dual;

select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;

select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;

select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;

select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;

select dbms_metadata.get_ddl('TABLE','SQLEXPERT_PLAN1','SYSTEM') from dual;

select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;

select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;

select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION') from dual;

select dbms_metadata.get_ddl('PACKAGE','DBMS_METADATA','SYS') from dual;