Generating DDL for creating an index

posted May 2, 2012, 6:09 AM by Sachchida Ojha   [ updated May 2, 2012, 6:10 AM ]
You might need to regenerate the DDL for creating an index already exists in the database and then use the DDL script to create the same index in other environments. There actually a couple of ways you can do this.


1. Using dbms_metadata package.

SQL> select dbms_metadata.get_ddl('INDEX','v_index_name') from dual;

2. Use third part tools like TOAD to geneerate the DDL.

3. Using OEM to generate the DDL

If you are using dbms_metadata.get_ddl method to generate the DDL, you definitely want to format the output nicely (wrapped without inconveniently word breaks) then

SQL> set linesize 80
SQL> col xyz format a100 word_wrapped
SQL> col x format a200 word_wrapped
SQL> select dbms_metadata.get_ddl('INDEX','v_index_name') from dual;

You can also use SESSION_TRANSFORM procedure to modify or customize the output generated by GET_DDL procedure.

For Example
SQL> begin
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
end;
/
;


Comments