Posted by Just a quick tip on using DBMS_METADATA that caught me out the other day. If you have a table and you wish to get the DDL that is “related” to the table, for example object grants or audit settings etc, then there is a GET_DEPENDENT_DDL API to do this. For example, to get the object level grants for a table T:
SQL> create table t ( x int primary key, c clob );
Table created.
SQL> grant select on t to scott;
Grant succeeded.
SQL> select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','T',USER) from dual;
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','T',USER)
--------------------------------------------------------------------------------
GRANT SELECT ON "MCDONAC"."T" TO "SCOTT"
You probably feel that this API would then also be the perfect candidates for getting the indexes, because they are also dependent on the table. However, lets look at the output:
SQL> create table t ( x int primary key, c clob );
Table created.
SQL> select dbms_metadata.get_dependent_ddl('INDEX','T',USER) from dual;
DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','T',USER)
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "MCDONAC"."SYS_C0069572" ON "MCDONAC"."T" ("X")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS"
CREATE UNIQUE INDEX "MCDONAC"."SYS_IL0005278752C00002$" ON "MCDONAC"."T" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE( INITIAL 65536 NEXT 1048576 MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARALLEL (DEGREE 0 INSTANCES 0)
DBMS_METADATA is perhaps overly thorough 
in what it defines as an index, because it will also output some DDL for the LOB index that associated with a LOB on the table. Clearly that DDL will not be valid because we don’t explicitly create LOB indexes – they are an artefact of the table. The simple workaround here is to loop through the data dictionary to get the indexes you want if you need that separation, eg
SQL> select dbms_metadata.get_ddl('INDEX',index_name,user)
2 from user_indexes
3 where table_name = 'T'
4 and index_type != 'LOB';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,USER)
---------------------------------------------------------------------------
CREATE UNIQUE INDEX "MCDONAC"."SYS_C0069574" ON "MCDONAC"."T" ("X")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS"
or whatever dictionary query you need to isolate the particular index objects you wish to extract. For example, you could join to USER_CONSTRAINTS to exclude indexes associated with constraints etc. All of which brings me to my usual question:
“Where is your source code control system for DDL??!?!”
Happy DDL-ing!
Learning is not a spectator sport 