Forum Stats

  • 3,728,709 Users
  • 2,245,678 Discussions
  • 7,853,708 Comments

Discussions

Oracle index or partition is in unusable state

2806078
2806078 Member Posts: 37

Hello,

I have an Oracle 11g R2 online dg (not clustered) and i've receving the following error:

ORA-01502: index 'xxx.my_index_name' or partition of such index is in unusable state

Now this was true for one of the partitions so i rebuilt it:

alter index xxx.my_index_name rebuild partition P_somedate;

But i'm stil receiving the error message and when i do a:

select * from dba_ind_partitions where status = 'UNUSABLE'; no rows are returned?

I have noticed because the db is quite active that when this does happen peformance suffers a great deal.

What am i doing wrong, is this a timing issue or something or there another query that can verify these partitions are ok?

thank you.

Mustafa_KALAYCI2806078

Best Answer

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited April 2017 Accepted Answer

    run this query:

    SELECT 'ALTER INDEX '||OWNER||'.'|| INDEX_NAME ||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE'UNION ALLSELECT  'ALTER INDEX '||INDEX_OWNER||'.'|| INDEX_NAME ||' REBUILD PARTITION ' ||PARTITION_NAME ||'ONLINE ;'  FROM SYS.DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE'UNION ALLSELECT 'ALTER INDEX '|| INDEX_OWNER || '.'|| INDEX_NAME || ' REBUILD SUBPARTITION ' || SUBPARTITION_NAME || ' ONLINE;' FROM   DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE';

    this will give you all unusable indexes/partitions/subpartitions

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited April 2017
    2806078 wrote:Hello,I have an Oracle 11g R2 online dg (not clustered) and i've receving the following error:ORA-01502: index 'xxx.my_index_name' or partition of such index is in unusable stateNow this was true for one of the partitions so i rebuilt it:alter index xxx.my_index_name rebuild partition P_somedate;But i'm stil receiving the error message and when i do a:select * from dba_ind_partitions where status = 'UNUSABLE'; no rows are returned?I have noticed because the db is quite active that when this does happen peformance suffers a great deal.What am i doing wrong, is this a timing issue or something or there another query that can verify these partitions are ok?thank you.

    What do you mean you get the same error? Is it really the same index being reported?

    This error occurs whenever Oracle needs to use an index that's unusable, it occurs because you've done something to make the index unusable. Perhaps you are doing partition maintenance that makes the index (or partitions off) unusable

  • MsJ
    MsJ Member Posts: 936
    edited April 2017
    2806078 wrote:Hello,I have an Oracle 11g R2 online dg (not clustered) and i've receving the following error:ORA-01502: index 'xxx.my_index_name' or partition of such index is in unusable stateNow this was true for one of the partitions so i rebuilt it:alter index xxx.my_index_name rebuild partition P_somedate;But i'm stil receiving the error message and when i do a:select * from dba_ind_partitions where status = 'UNUSABLE'; no rows are returned?I have noticed because the db is quite active that when this does happen peformance suffers a great deal.What am i doing wrong, is this a timing issue or something or there another query that can verify these partitions are ok?thank you.

    Rebuild the indexes and reanalyse table statistics should fix your issue.

  • 2806078
    2806078 Member Posts: 37
    edited April 2017

    Hi,

    Thanks for the replies,

    i've rebuilt the only table that was in "unusable" status, but i'm still receiving the error, not just for this table but others.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited April 2017 Accepted Answer

    run this query:

    SELECT 'ALTER INDEX '||OWNER||'.'|| INDEX_NAME ||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE'UNION ALLSELECT  'ALTER INDEX '||INDEX_OWNER||'.'|| INDEX_NAME ||' REBUILD PARTITION ' ||PARTITION_NAME ||'ONLINE ;'  FROM SYS.DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE'UNION ALLSELECT 'ALTER INDEX '|| INDEX_OWNER || '.'|| INDEX_NAME || ' REBUILD SUBPARTITION ' || SUBPARTITION_NAME || ' ONLINE;' FROM   DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE';

    this will give you all unusable indexes/partitions/subpartitions

  • Unknown
    edited April 2017

    i've rebuilt the only table that was in "unusable" status, but i'm still receiving the error, not just for this table but others.

    Except the exception is NOT about the table - it is about an index.

    Misleading statements like the above are why you need to SHOW US, not tell us:

    1. WHAT you do

    2. HOW you do it

    3. WHAT results you get

    We now have NO IDEA whether you really 'rebuilt the only table' or if you rebuilt an index. At this point we want to see for ourselves - post a copy of ALL COMMANDS you executed along with the actual results.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited April 2017
    2806078 wrote:Hi,Thanks for the replies,i've rebuilt the only table that was in "unusable" status, but i'm still receiving the error, not just for this table but others. 

    [[email protected] ~]$ oerr ora 1502

    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

    rebuild INDEX; not table

  • 2806078
    2806078 Member Posts: 37
    edited April 2017

    Hi,

    As per my original post i identified the index and then rebuilt it:

    alter index xxx.my_index_name rebuild partition P_somedate;

    Which was succesful but my application is still complaining about an index ORA-01502, so i wanted to make sure my query to idenify the indexes and partitions was correct. I'm marking @Mustafa KALAYCI as correct.

    Thank you.

    Mustafa_KALAYCI
  • Unknown
    edited April 2017

    As per my original post i identified the index and then rebuilt it:alter index xxx.my_index_name rebuild partition P_somedate;Which was succesful but my application is still complaining about an index ORA-01502

    Please explain how that could have been 'successful' if it did NOT solve the problem? Clearly you did NOT identify the correct index.

  • 2806078
    2806078 Member Posts: 37
    edited April 2017

    Upon further investigation it looks like the company that wrote this application must have written something to automatically rebuild the indexes and it works most of the time.

    What would cause the indexes to become unusable so often, it looks like it happens approximately every 120 mins or so.

  • Unknown
    edited April 2017

    This thread has ALREADY been ANSWERED.

    If you have other questions create a new thread.

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,753 Blue Diamond
    edited April 2017

    >>What would cause the indexes to become unusable so often

    Code that explicitly sets Indexes / Partitions UNUSABLE.

    Direct Path SQLLoader session that fails. 

    Partition DDL (SPLIT, MOVE etc) without the UPDATE INDEXES clause.

    Hemant K Chitale

    2806078
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited April 2017
    2806078 wrote:Upon further investigation it looks like the company that wrote this application must have written something to automatically rebuild the indexes and it works most of the time.What would cause the indexes to become unusable so often, it looks like it happens approximately every 120 mins or so.

    Consult the vendor. If they were preparred for indexes to be unusable then they must know why, it's their code doing it.

    2806078
This discussion has been closed.