If you omit schema, then Oracle Database assumes the index is in your own schema.DROP INDEX <schema>.<index_name>;
E.g. Below statement drops an index namde idx1.
orDROP INDEX idx1;
DROP INDEX user.idx1;
Drop Index
Drop Index
Below is the step on how to drop index in Oracle.
E.g. Below statement drops an index namde idx1.
E.g. Below statement drops an index namde idx1.
Oracle Database Administration Forums
http://www.oracle-forums.com/
http://www.oracle-forums.com/
Re: Drop Index
Drop an Index
The syntax for dropping an index is:
The syntax for dropping an index is:
For example:DROP INDEX index_name;
In this example, we're dropping an index called idx_contact.DROP INDEX idx_contact;
Re: Drop Index
For Oracle Database 10g, you can find below how to drop an index.
What's DROP INDEX for?
We use the statement DROP INDEX to remove an index or domain index from the database.
As the index is dropped, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
Prerequisites:
The index must be in your own schema or you must have the DROP ANY INDEX system privilege in order to perform the DROP INDEX statement.
SYNTAX:
DROP INDEX <schema>.<index_name> FORCE;
schema:
Specify the schema containing the index. If you omit schema, then Oracle Database assumes the index is in your own schema.
index:
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:
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.
Example:
Dropping an Index:
This statement drops an index named SALARY_IX owned by schema HR
SQL> DROP INDEX HR.SALARY_IX;
What's DROP INDEX for?
We use the statement DROP INDEX to remove an index or domain index from the database.
As the index is dropped, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
Prerequisites:
The index must be in your own schema or you must have the DROP ANY INDEX system privilege in order to perform the DROP INDEX statement.
SYNTAX:
DROP INDEX <schema>.<index_name> FORCE;
schema:
Specify the schema containing the index. If you omit schema, then Oracle Database assumes the index is in your own schema.
index:
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:
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.
Example:
Dropping an Index:
This statement drops an index named SALARY_IX owned by schema HR
SQL> DROP INDEX HR.SALARY_IX;
SQL> DROP INDEX HR.SALARY_IX;
Index dropped.
SQL>
Oracle Database Administration Forums
http://www.oracle-forums.com/
http://www.oracle-forums.com/