Home » SQL » Displaying the DDL for an index in Oracle

Displaying the DDL for an index in Oracle

You may be performing routine for maintenance, such as moving an index to a different tablespace and before you do so, you want to verify the current storage settings. You can use the DBMS_METADATA package to display the DDL required to recreate an index. If you’re using SQL*Plus, set the LONG variable to a value large enough to display all the output. Here is an example:

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('INDEX','SUPP_IDX1') from dual;

Here is a partial listing of the output:

CREATE INDEX "MV_MAINT"."SUPP_IDX1" ON "MV_MAINT"."SUPP" ("SUPP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS

To show all index DDL for a user, run this query:

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;

You can also display the DDL for a particular user. You must provide as input to the GET_DDL function the object type, object name, and schema; example:
select
dbms_metadata.get_ddl(object_type=>'INDEX', name=>'SUPP_IDX1', schema=>'INV')
from dual;