Tuesday, December 11, 2012

Generate DDL scripts using "DBMS_METADATA" package


To Generate all Tablespace DDL's:

set long 2000;
select dbms_metadata.get_ddl('TABLESPACE',tb.name) from v$tablespace tb ;

for 1 tablespace:
select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;


To Generate DDL for a USER :

set long 5000 linesize 280;
select dbms_metadata.get_ddl('USER', 'USER_NAME')  from dual;


To Generate DDL for all PROFILE's :

set long 5000;
select dbms_metadata.get_ddl('PROFILE',a.profile) from dba_profiles a;

For 1 profile:
 select dbms_metadata.get_ddl('PROFILE','PROFILE_NAME') from dual;


To Generate DDL for any TABLE :

set long 5000;
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','OWNER') from dual;


To Generate DDL for any INDEX :

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


To Generate all INDEX DDL’s on a TABLE:

select dbms_metadata.get_dependent_ddl('INDEX',’TABLE_NAME','OWNER') from dual;

The above command will generate all index ddl’s which are on table.

To Generate DDL for a Specific SEQUENCE :

set long 5000;
set linesize 200;
select dbms_metadata.get_ddl('SEQUENCE','SEQUENCE_NAME','OWNER_NAME') from dual;


To Generate DDL for a Specific TRIGGER:

set long 5000;
set linesize 200;
SQL> select dbms_metadata.get_ddl('TRIGGER','TRGGER_NAME','TRIGGER_OWNER') from dual;


Auto Scroll Stop Scroll