Displaying Index Code

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