This content has been marked as final. Show 18 replies
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
Thanks for the manual. Perhaps, we are not communicating... Please enlighten me if the following delete statement is not correct for removing the duplicate rows:
FROM <Table_Name> A
any(SELECT B.rowid FROM <Table_Name> B
B.KEY1 = A.KEY1
B.KEY2 = A.KEY2
B.KEY3 = A.KEY3
B.KEY4 = A.KEY4
I received the following error after running the above statement:
[Error] Execution (1: 8): ORA-01502: index 'HMMS.QA_ASSMNT_FEAT_PK' or partition of such index is in unusable state
how did duplicates get into the table?
was the PK disabled prior to the data load?
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
The duplicate key rows were inserted to the database by using the DIRECT parameter of the sql loader to specify direct path loading which bypasses integrity constraints. Since the issue exists in our production database and I am primarily an A/P w/ some responsibilities of DBA , I could really use your help to fix our production problem. I would really appreciate if you or any other guru could review my plan and code below for the fixes followed w/ some questions and give me your feedback/comments/suggestions as soon as possible:
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
p.s. Although, we only have a total of 35,736 rows of data from this table, but I want to make sure I do it right the first time.
Thanks a lot and any/all the help!
Do you have the original file? Is it a flat file? Do you have access to unix tools?
For these kinds of things at this level of volume, I usually just do something like:
sort filea > j1
sort -u filea > j2
diff j1 j2|more
By this time I can usually use an editor to remove the offenders (I often get dirty csv files with other things wrong, so this way makes sense for me before I even try to load). One could script it, I suppose. Or one could use external files and do a minus on j1 and j2 in Oracle. If you don't have the original file, you can make it by spooling a select * with an order by.
The faq has some other techniques: http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table
Thanks for the information. I find your approach interesting, but I am not sure where you are heading w/ your suggestion. BTW, I don't have access to the unix box where our production dB is at but I could request for it if necessary. The duplicate rows already exist in the production table. I don't understand how it would help by identifying the duplicate rows externally from unix. The problem is that the database won't allow me to delete the identified duplicate rows due to the offending index of the table in "un-usable" state and it won't allow me to rebuild the index either because of the "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found" error.
The problem is that the database won't allow me to delete the identified duplicate rows due to the offending index of the table in "un-usable" state and it won't allow me to rebuild the index either because of the "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found" error.
Yes - we know that. You are just repeating what you already said above three days ago. So I will repeat what I already said above
You can't use a query that tries to use the index - the index is not usable. 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.
You never posted what your 4 digit Oracle version is or even how many rows there are in the problem table.
Another way to get rid of the duplicates is to use DML error logging: create a new table with a unique index and create an error logging table for that table.
Then insert the rows from your problem table into the new table and let Oracle put the duplicate rows into the error logging table.
Then your new table will have unique rows. See DBMS_ERRORLOG package in the packages and types doc.
And see the error logging clause in the SQL Language doc - it has examples of how to do this
Our Oracle database version is 10.1.0.5.0. There is a total of #1976 duplicate rows in the table.
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.
Do I need to back up and reload the data before and after dropping and creating the index?
The answers depend on more things you haven't told us. Is this a one time thing or something you will be doing repeatedly? Is this unique important data or is it replaceable from elsewhere? Do you have a way of identifying which rows with identical keys you want to keep, or are the entire rows identical? Are you using sqlplus, or other tools also?
You can see the [url http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_5010.htm#SQLRF01209]create index syntax in the docs. If you don't specify unique, it isn't. Note that some indices are created implicitly (as when primary keys are specified).
I think the suggestions rp made are the most useful in the long term. The faq I pointed at are useful if you want to simply delete the duplicates with earlier or later rowid's. There is more than one way to do this, only you can make the decision as to which is best.