Get Metadata of Oracle Objects

 dbms_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','MANOJ') from dual;

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','MANOJ,'OWNER') from dual;

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';

select 'select dbms_metadata.get_ddl(''CONSTRAINT'',''' || CONSTRAINT_NAME || ''',''' || owner|| ''') from dual;' from dba_constraints where OWNER='AMIT';