Orchid wrote:eliminate the duplicate keys; then rebuild the index
I received the ORA-01502: index or partition of such index is in un-usable state from one of our Oracle forms, and the problem was caused by using SQL Loader w/ the "DIRECT" load option. As a result of that, the system won't allow any updates including insert/delete to the problematic table w/ the index in un-usable state. I am trying to fix the problem but so far no luck:
When I tried to delete the duplicate rows from the table, I received the ORA-01502: index or partition of such index is in un-usable state error message;
When I tried to rebuild the index by using the following command:
ALTER INDEX index_name REBUILD
I received the "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
It appears that your alternative is to drop the INDEX, so you can remove the duplicates.
01502, 00000, "index '%s.%s' or partition of such index is in unusable state" // MERGE: 1489 RENUMBERED TO 1502 // *Cause: An attempt has been made to access an index or index partition // that has been marked unusable by a direct load or by a DDL // operation // *Action: DROP the specified index, or REBUILD the specified index, or // REBUILD the unusable index partition
But wait, how about data? Don't I need to back up and reload the data? Do I need to drop and recreate the table? Anything else I should be taking into considerations?
1 - Drop the offending index by using the following command: DROP INDEX <offending index name> 2 - Run the following script to detect duplicate rows: SELECT * FROM <Table_Name> A WHERE A.rowid > any(SELECT B.rowid FROM <Table_Name> B WHERE B.KEY1 = A.KEY1 and B.KEY2 = A.KEY2 and B.KEY3 = A.KEY3 and B.KEY4 = A.KEY4 ); 3. Run the following script to remove the duplicates: DELETE FROM <Table_Name> A WHERE A.rowid > any(SELECT B.rowid FROM <Table_Name> B WHERE B.KEY1 = A.KEY1 and B.KEY2 = A.KEY2 and B.KEY3 = A.KEY3 and B.KEY4 = A.KEY4 ); 4 - Rebuild the index by using the following line command: ALTER INDEX index_name REBUILD
How do I create a nonunique index (assuming) after dropping the unique index?
Drop the index. If you need an index to make your de-dup query work then create a nonunique one, get rid of the duplicates and then recreate the unique index.