Databases Reference
In-Depth Information
large number of partitions and even subpartitions, it makes more sense as to why you'd want to extract
the DDL using some kind of a tool or utility. Let's review the ways you can extract DDL for indexes in the
following sections.
Using the DBMS_METADATA Package
The easiest way to get the DDL for getting the DDL for the creation of an existing index is to use the
DBMS_METADATA
package supplied by Oracle. You can employ the
DBMS_METADATA
package to extract the
DDL for other objects besides indexes. The following is an example that shows how to get the DDL for
creating an index named
EMP_NAME_IDX
that's part of the HR schema:
SQL> select dbms_metadata.get_ddl('INDEX','SALES_PROMO_BIX') from dual;
DBMS_METADATA.GET_DDL('INDEX','SALES_PROMO_BIX')
--------------------------------------------------------------------------------
CREATE BITMAP INDEX "SYS"."SALES_PROMO_BIX" ON "SYS"."SALES" ("PROMO_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "SALES_1995"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "SALES_1996"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
...
SQL>
Once you get the DDL for an index, use that DDL to create your index.
SQL> CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME",
"FIRST_NAME")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
5* TABLESPACE "EXAMPLE"
