bms_metadata.get_ddl package, How to get ddl's of object's in the database

DDL 's of Objects in a Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME') from dual;


SQL> set lin 1000
SQL> set pagesize 9999
SQL> set long 9999
SQL> select dbms_metadata.get_ddl('TABLE','AMIT') from dual;

  CREATE TABLE "OWNER"."AMIT"

   (    "A" VARCHAR2(26),

        "B" VARCHAR2(10),

        "C" VARCHAR2(10),

        "D" VARCHAR2(20),

        "E" VARCHAR2(10),

        "F" DATE,

        "G" NUMBER,

        "H" VARCHAR2(20),

        "I" VARCHAR2(20),

        "J" VARCHAR2(5),

        "K" NUMBER,

        "L" NUMBER,

        "M" NUMBER,

        "N" NUMBER,

        "O" DATE,

        "P" DATE,

        "Q" VARCHAR2(255),

        "R" VARCHAR2(255),

        "S" NUMBER,

        "T" NUMBER,

        "U" VARCHAR2(10),

        "V" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

;

DDL 's of Objects in a Any Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','USERNAME') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','USERNAME') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','USERNAME') from dual;

SQL> set lin 1000

SQL> set pagesize 9999

SQL> set long 9999

SQL> select dbms_metadata.get_ddl('TABLE','AMIT','OWNER') from dual;

  CREATE TABLE "OWNER"."AMIT"
   (    "A" VARCHAR2(26),
        "B" VARCHAR2(10),
        "C" VARCHAR2(10),
        "D" VARCHAR2(20),
        "E" VARCHAR2(10),
        "F" DATE,
        "G" NUMBER,
        "H" VARCHAR2(20),
        "I" VARCHAR2(20),
        "J" VARCHAR2(5),
        "K" NUMBER,
        "L" NUMBER,
        "M" NUMBER,
        "N" NUMBER,
        "O" DATE,
        "P" DATE,
        "Q" VARCHAR2(255),
        "R" VARCHAR2(255),
        "S" NUMBER,
        "T" NUMBER,
        "U" VARCHAR2(10),
        "V" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

;

Script to Generate DDL 's of Various Objects of database :-

Script for DDL 's of All Indexes of database:-

SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || index_name|| ''' ) from dual;'  from user_indexes;


SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='INDEX';

SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='INDEX' and owner not in ('SYS','SYSTEM');

Script for DDL's of all Tables of database:-

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || table_name|| ''' ) from dual;'  from user_tables;

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TABLE';

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TABLE' and owner not in ('SYS','SYSTEM');

Script for DDL's of All Procedures of database:-

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE';

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner='OWNER_NAME';

Script for DDL's of All Functions of database :-

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION';

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner='OWNER_NAME';

Script for DDL's of All Triggers of database:-

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER';

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner='OWNER_NAME';

Script for DDL's of All Views of database:-

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW';

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner='OWNER_NAME';

I hope this article helped you.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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;

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;