The DROP_INDEX stored procedure is used to drop a single index or all indexes on the specified table except for the Primary Key (PK) index.
The Primary Key Case Insensitive (PKCI) function-based index will only be dropped if provided as input using the INDEX_NAME parameter to specifically drop the PKCI index. If only the table name is specified, then all indexes on the table will be dropped except the PK and PKCI indexes.
If an index name is provided, then only the specified index will be dropped.
The DROP_INDEX procedure will set the DDL_LOCK_TIMEOUT parameter to 60 seconds so will wait up to one minute for a DDL lock to drop an index. Note that this could result in longer waits when trying to drop an index that is actively being used.
| Parameter | Description | |
|---|---|---|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The schema name that owns the table with the index(es) being dropped. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the table with the index(es) being dropped. | |
| INDEX_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The name of the index being dropped. | |
Exceptions
- FAILED_CHECK
- RESOURCE_BUSY – Could not drop index due to resource lock wait. ORA-00054
- PK_INDEX – Cannot drop index used to enforce primary key constraint. ORA- 02429.
- OTHERS
Example
EXEC IN_DB_UTIL.DROP_INDEX('INUSER', TABLE_NAME, [INDEX_NAME]);
Example Output
SQL> EXEC IN_DB_UTIL.DROP_INDEX('INUSER', 'IN_AUDIT', 'AUDIT_IDX_01');
DROP INDEX INUSER.AUDIT_IDX_01
PL/SQL procedure successfully completed.