This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 14, 2012 9:24 AM by jgarry RSS

How to fix ORA-01502: index or partition of such index is in usable state?

user389605 Newbie
Currently Being Moderated
Greetings,

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

Any help, suggestions or advice on this would be greatly appreciated.

Thanks millions in advance!
  • 1. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    sb92075 Guru
    Currently Being Moderated
    Orchid wrote:
    Greetings,

    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
    eliminate the duplicate keys; then rebuild the index
  • 2. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    user389605 Newbie
    Currently Being Moderated
    How to eliminate the duplicate keys?
  • 3. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    sb92075 Guru
    Currently Being Moderated
    Orchid wrote:
    How to eliminate the duplicate keys?
    Are you capable to actually Read The Fine Manual & understand it?

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8005.htm#i2117787
  • 4. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    user389605 Newbie
    Currently Being Moderated
    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:

    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
    );

    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
  • 5. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    sb92075 Guru
    Currently Being Moderated
    how did duplicates get into the table?
    was the PK disabled prior to the data load?
    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
    It appears that your alternative is to drop the INDEX, so you can remove the duplicates.
  • 6. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    rp0428 Guru
    Currently Being Moderated
    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.
  • 7. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    user389605 Newbie
    Currently Being Moderated
    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:
    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
    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?

    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!
  • 8. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    sb92075 Guru
    Currently Being Moderated
    ALTER INDEX index_name REBUILD
    since the INDEX was dropped in step #1, no INDEX exists to rebuild;
    so this step will produce no new INDEX.
  • 9. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    jgarry Guru
    Currently Being Moderated
    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
  • 11. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    user389605 Newbie
    Currently Being Moderated
    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.
  • 12. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    rp0428 Guru
    Currently Being Moderated
    >
    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.
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_errlog.htm

    And see the error logging clause in the SQL Language doc - it has examples of how to do this
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#sthref9022
  • 13. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    user389605 Newbie
    Currently Being Moderated
    Our Oracle database version is 10.1.0.5.0. There is a total of #1976 duplicate rows in the table.
    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. 
    How do I create a nonunique index (assuming) after dropping the unique index?

    Do I need to back up and reload the data before and after dropping and creating the index?

    Thanks.
  • 14. Re: How to fix ORA-01502: index or partition of such index is in usable state?
    jgarry Guru
    Currently Being Moderated
    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.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points