This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Sep 15, 2009 12:57 PM by Randolf Geist RSS

indexes in unusable state

586983 Newbie
Currently Being Moderated
Hi All,

I am having oracle 10.2.0.2 on Solaris 10. There are two indexes in one of the table which go into unsuable state everyday..Inspite of rebuilding them. So the problem is i have to rebuild the indexes in 1/2 days..
I even tried to drop and recreate them, but no use..

whn i check my alert log, it gives me error like:

Sat Jan 10 04:00:05 2009
GATHER_STATS_JOB encountered errors. Check the trace file.
Sat Jan 10 04:00:05 2009
Errors in file /opt/app/oracle/admin/casino/bdump/casino_j001_7035.trc:
ORA-20000: index "CASINO"."UK_NPG_GAMECODE" or partition of such index is in unusable state


Please help me in solving this issue...


Thanks in advance,
Arundhati
  • 1. Re: indexes in unusable state
    Pavan DBA Expert
    Currently Being Moderated
    hi arundhati, are you performing any table re-org activity using alter table <table_name> move tablespace <tablespace_name> command.
  • 2. Re: indexes in unusable state
    586983 Newbie
    Currently Being Moderated
    Hi,

    No till now re-org has not been done. I havent moved the table to different tablespace.


    Thanks,
    Arundhati
  • 3. Re: indexes in unusable state
    Pavan DBA Expert
    Currently Being Moderated
    did you scheduled any job for gathering schema statistics? it seems the problem is with the index partition. the indexes that you are specifying are partitioned indexes? if so try to rebuild only the partition specified in your alert log file.
  • 4. Re: indexes in unusable state
    Surachart Opun Oracle ACE
    Currently Being Moderated
    Check trace file on /opt/app/oracle/admin/casino/bdump/casino_j001_7035.trc.

    You make sure you don't move table on some job...or not...

    split partition?

    exchange partition?
  • 5. Re: indexes in unusable state
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user583980 wrote:
    Hi All,

    I am having oracle 10.2.0.2 on Solaris 10. There are two indexes in one of the table which go into unsuable state everyday..Inspite of rebuilding them. So the problem is i have to rebuild the indexes in 1/2 days..
    I even tried to drop and recreate them, but no use..

    whn i check my alert log, it gives me error like:

    Sat Jan 10 04:00:05 2009
    GATHER_STATS_JOB encountered errors. Check the trace file.
    Sat Jan 10 04:00:05 2009
    Errors in file /opt/app/oracle/admin/casino/bdump/casino_j001_7035.trc:
    ORA-20000: index "CASINO"."UK_NPG_GAMECODE" or partition of such index is in unusable state


    Please help me in solving this issue...
    Arundhati,

    as already mentioned, there is probably some partition maintenance operation performed which makes the indexes unusable. Are these local indexes or global (partitioned) indexes? I assume these are global, unpartitioned indexes according to your description, but it would be good to clarify.

    The following partition maintenance operations on a heap-organized table make LOCAL and/or GLOBAL indexes unusable if you don't use the UPDATE \[GLOBAL\] INDEXES clause:

    * Adding Partitions (to a hash-partitioned table, not range-partitioned): LOCAL, GLOBAL
    * Coalescing Partitions (of a hash-partitioned table): LOCAL, GLOBAL
    * Dropping Partitions (if dropped partition is not empty): GLOBAL
    * Exchange Partitions: GLOBAL
    * Merging Partitions: GLOBAL, LOCAL
    * Moving Partitions: GLOBAL, LOCAL
    * Splitting Partitions: GLOBAL, LOCAL

    For more information, you could refer to the documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2703

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 6. Re: indexes in unusable state
    586983 Newbie
    Currently Being Moderated
    Hi,

    No till now re-org has not been done. I havent moved the table to different tablespace.


    Thanks,
    Arundhati
  • 7. Re: indexes in unusable state
    586983 Newbie
    Currently Being Moderated
    Hi,


    Sorry, that was by mistake posted again....
  • 8. Re: indexes in unusable state
    3530 Explorer
    Currently Being Moderated
    Hi Randolf,

    I did not understood your comments

    "as already mentioned, there is probably some partition maintenance operation performed which makes the indexes unusable. Are these local indexes or global (partitioned) indexes? I assume these are global, unpartitioned indexes according to your description, but it would be good to clarify.

    The following partition maintenance operations on a heap-organized table make LOCAL and/or GLOBAL indexes unusable if you don't use the UPDATE [GLOBAL] INDEXES clause: "

    Can you explain me why partition maintenance (add partition would make index unusable?).

    Here is small experiment on adding partition.


    SQL> CREATE TABLE sales
    2 ( invoice_no NUMBER,
    3 sale_year INT NOT NULL,
    4 sale_month INT NOT NULL,
    5 sale_day INT NOT NULL )
    6 PARTITION BY RANGE ( sale_year)
    7 ( PARTITION sales_1999 VALUES LESS THAN ( 1999 ),
    8 PARTITION sales_2000 VALUES LESS THAN ( 2000 ),
    9 PARTITION sales_2001 VALUES LESS THAN ( 2001 ),
    10 PARTITION sales_2002 VALUES LESS THAN ( 2002 ))
    11 ;

    Table created.

    SQL>
    SQL> insert into sales values (1,1999, 12,1);

    1 row created.

    SQL> insert into sales values (2,2000, 11,1);

    1 row created.

    SQL> insert into sales values (3,2000, 12,1);

    1 row created.

    SQL> insert into sales values (4,2001, 10,1);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> create index sales_indx1 on sales (sale_month)
    2 tablespace users
    3 local
    4 (partition sales_1999 tablespace users,
    5 partition sales_2000 tablespace users,
    6 partition sales_2001 tablespace users,
    7 partition sales_2002 tablespace users);

    Index created.

    SQL>
    SQL>
    SQL>
    SQL> select index_name, status from user_indexes where index_name = 'SALES_INDX1';

    INDEX_NAME STATUS
    ------------------------------ --------
    SALES_INDX1 N/A


    SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'SALES_INDX1'
    2 /

    INDEX_NAME PARTITION_NAME STATUS
    ------------------------------ ------------------------------ --------
    SALES_INDX1 SALES_1999 USABLE
    SALES_INDX1 SALES_2000 USABLE
    SALES_INDX1 SALES_2001 USABLE
    SALES_INDX1 SALES_2002 USABLE

    SQL>


    SQL> alter table sales add partition sales_2003 VALUES LESS THAN ( 2003);

    Table altered.

    SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'SALES_INDX1'
    2 /

    INDEX_NAME PARTITION_NAME STATUS
    ------------------------------ ------------------------------ --------
    SALES_INDX1 SALES_1999 USABLE
    SALES_INDX1 SALES_2000 USABLE
    SALES_INDX1 SALES_2001 USABLE
    SALES_INDX1 SALES_2002 USABLE
    SALES_INDX1 SALES_2003 USABLE

    SQL> select index_name, status from user_indexes where index_name = 'SALES_INDX1';

    INDEX_NAME STATUS
    ------------------------------ --------
    SALES_INDX1 N/A

    SQL>


    Dilip Patel.
  • 9. Re: indexes in unusable state
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Dilip Patel wrote:
    Can you explain me why partition maintenance (add partition would make index unusable?).
    Dilip,

    if you read my post carefully, you'll notice the following:

    * Adding Partitions (to a hash-partitioned table, not range-partitioned): LOCAL, GLOBAL

    I think this answers your question. Your test case uses range partitioning, which doesn't invalidate any indexes when adding a new partition.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 10. Re: indexes in unusable state
    3530 Explorer
    Currently Being Moderated
    Hi,

    Test case on hash partitioned table with local index.


    SQL> CREATE TABLE sales_hash
    2 ( invoice_no NUMBER,
    3 sale_year INT NOT NULL,
    4 sale_month INT NOT NULL,
    5 sale_day INT NOT NULL )
    6 partition by hash (invoice_no)
    7 (partition part_001 tablespace users,
    8 partition part_002 tablespace users,
    9 partition part_003 tablespace users,
    10 partition part_004 tablespace users);

    Table created.

    SQL>
    SQL> insert into sales_hash values (1,1999, 12,1);

    1 row created.

    SQL> insert into sales_hash values (2,2000, 11,1);

    1 row created.

    SQL> insert into sales_hash values (3,2000, 12,1);

    1 row created.

    SQL> insert into sales_hash values (4,2001, 10,1);

    1 row created.

    SQL> insert into sales_hash values (5,2002, 1,1);

    1 row created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL> create index sales_hash_indx1 on sales_hash(sale_year)
    2 local
    3 (partition part_001 tablespace users,
    4 partition part_002 tablespace users,
    5 partition part_003 tablespace users,
    6 partition part_004 tablespace users);

    Index created.

    SQL> alter table sales_hash add partition part_005 tablespace users;

    Table altered.

    SQL> select index_name, partition_name, status from user_ind_partitions where index_name = 'SALES_HASH_INDX1';

    INDEX_NAME PARTITION_NAME STATUS
    ------------------------------ ------------------------------ --------
    SALES_HASH_INDX1 PART_001 USABLE
    SALES_HASH_INDX1 PART_002 USABLE
    SALES_HASH_INDX1 PART_003 USABLE
    SALES_HASH_INDX1 PART_004 USABLE
    SALES_HASH_INDX1 PART_005 USABLE

    SQL>
    SQL> select index_name, status from user_indexes where index_name = 'SALES_HASH_INDX1';

    INDEX_NAME STATUS
    ------------------------------ --------
    SALES_HASH_INDX1 N/A

    SQL>

    Your comment !!!!!

    Dilip Patel.
  • 11. Re: indexes in unusable state
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Dilip Patel wrote:
    Hi,

    Test case on hash partitioned table with local index.

    Your comment !!!!!
    Dilip,

    I really appreciate that you take the time to test this for yourself, it's always a very good idea to generate a test case and to not believe what someone just writes. As Jonathan Lewis uses to say: "Never believe all you read". In addition by sharing your findings everyone can participate and gain knowledge.

    In this particular case the test case is probably a bit flawed, because your data is not representative enough.

    You can simply gather statistics before and after adding the partition:
    exec dbms_stats.gather_table_stats(null, 'SALES_HASH', cascade=>true, granularity=>'ALL')
    to find out that some of your partitions probably don't contain data and after adding the partition no re-distribution has taken place:
    select partition_name, num_rows from user_tab_partitions 
    where table_name = 'SALES_HASH';
    Therefore Oracle was smart enough to leave the indexes in a usable state.

    Things look probably different if you use something like this:
    insert into sales_hash (
    invoice_no,
    sale_year,
    sale_month,
    sale_day
    )
    select rownum,
    2000 + round(dbms_random.value(0, 8)) as sale_year,
    trunc(dbms_random.value(1, 13)) as sale_month,
    trunc(dbms_random.value(1, 29)) as sale_day
    from dual
    connect by level <= 1000;
    and then add a partition.

    By the way, when posting such test cases it's a good idea to include the version you've used to test this, as many things change between the different versions.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 12. Re: indexes in unusable state
    3530 Explorer
    Currently Being Moderated
    H Randolf,

    Thanks, I have tested on 10.2.0.3 and with a bit larger data, I could see index UNUSABLE. I understood your point.

    Now back to original issue of the thread.

    Generally, hash partitions are fixed and would not be added thru application, if it is then it's a special case !!!!

    I generally came across dynamic addition of partition in case of range, where you would add latest partitions (new date range) and remove old dates.

    Other case where index would become unusable (in case of range) also is load data using DIRECT INSERT method.

    Thanks,

    Dilip.
  • 13. Re: indexes in unusable state
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Dilip Patel wrote:

    Generally, hash partitions are fixed and would not be added thru application, if it is then it's a special case !!!!

    I generally came across dynamic addition of partition in case of range, where you would add latest partitions (new date range) and remove old dates.
    As mentioned, there are other partition maintenance operations (like merges and splits of range partitions) which can also invalidate indexes.
    Other case where index would become unusable (in case of range) also is load data using DIRECT INSERT method.
    Dilip,

    I'm not sure what you mean by "DIRECT INSERT", but it's not correct that a direct-path insert (INSERT /*+ APPEND */) invalidates any usable indexes. A direct-path insert maintains all usable indexes, and therefore generates undo for that part of the operation (whereas the actual insert into the table doesn't generate undo).

    There is an option to mark indexes unusable when performing direct-path loads using SQL*Loader (SKIP_INDEX_MAINTENANCE) but I doubt that you refer to that if you say "...in case of range...".

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 14. Re: indexes in unusable state
    586983 Newbie
    Currently Being Moderated
    Hi All,

    Its a normal index, its not a partitioned index..

    i am really not getting what can be the reason behind this...

    Please help.

    Thanks
    Arundhati
1 2 Previous Next

Legend

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