Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E17118-03
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF



Use the DROP INDEX statement to remove an index or domain index from the database.

When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.

When you drop a global partitioned index, a range-partitioned index, or a hash-partitioned index, all the index partitions are also dropped. If you drop a composite-partitioned index, then all the index partitions and subpartitions are also dropped.

In addition, when you drop a domain index:


The index must be in your own schema or you must have the DROP ANY INDEX system privilege.



Description of drop_index.gif follows
Description of the illustration drop_index.gif



Specify the schema containing the index. If you omit schema, then Oracle Database assumes the index is in your own schema.


Specify the name of the index to be dropped. When the index is dropped, all data blocks allocated to the index are returned to the tablespace that contained the index.

Restriction on Dropping Indexes You cannot drop a domain index if the index or any of its index partitions is marked IN_PROGRESS.


FORCE applies only to domain indexes. This clause drops the domain index even if the indextype routine invocation returns an error or the index is marked IN PROGRESS. Without FORCE, you cannot drop a domain index if its indextype routine invocation returns an error or the index is marked IN PROGRESS.


Dropping an Index: Example This statement drops an index named ord_customer_ix_demo, which was created in "Compressing an Index: Example":

DROP INDEX ord_customer_ix_demo;