Displaying Index Code
From time to time you’ll need to drop an index. This could be because of an obsolete application or
you’ve established that an index is no longer used. Prior to dropping an index, we recommend that you
generate the data definition language (DDL) that would be required to re-create the index. This allows
you to re-create the index (as it was before it was dropped) in the event that dropping the index has a
detrimental impact on performance and needs to be re-created.
Use the DBMS_METADATA.GET_DDL function to display an object’s DDL. Make sure you set the LONG
variable to an appropriate value so that the returned CLOB value is displayed in its entirety. For
example,
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;
Here is the output:
DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------
CREATE INDEX "MV_MAINT"."ADDR_FK1" ON
"MV_MAINT"."ADDRESS" ("CUST_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS STORAGE(INITIAL 1048576
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1
