1 2 Previous Next 29 Replies Latest reply: Sep 15, 2009 2:57 PM by Randolf Geist Go to original post RSS
      • 15. Re: indexes in unusable state
        3530
        Hi Arundhati,

        Is it one off incident with this Index ? or it is regular ?

        Try to find out what DML or any DDL operations are going on the base table.

        Dilip Patel.
        • 16. Re: indexes in unusable state
          Randolf Geist
          user583980 wrote:
          Its a normal index, its not a partitioned index..
          Arundhati,

          could you please try to confirm: I assume you mean it's an unpartitioned global index on a partitioned table. So is it true that the corresponding table is partitioned or not?

          The index doesn't have to partitioned in the same way as the table or not partitioned at all. Such an index is called a "GLOBAL" index, whereas a "LOCAL" index is equi-partitioned with the corresponding table partitions.

          So if the table is partitioned, then very likely some of the partition maintenance operations on the table mentioned above can make the GLOBAL index unusable if you don't use the UPDATE \[GLOBAL\] INDEXES clause as part of the maintenance operation (e.g. ALTER TABLE ... SPLIT PARTITION ... UPDATE GLOBAL INDEXES).

          If the corresponding table is not partitioned, too, then there only a few scenarios how the index can become unusable, among them are:

          * Reorganisation of the table (e.g. ALTER TABLE ... MOVE ...)
          * Setting the index explicitly unusable e.g. as part of a load operation (ALTER INDEX ... UNUSABLE)
          * Using SQL*Loader with the "SKIP_INDEX_MAINTENANCE" option

          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/
          • 17. Re: indexes in unusable state
            586983
            Hi,

            Yes, the table is partitioned..how do i check which type of partition it is?
            pls tel me how can i solvethis problem...


            Thanks
            Arundhati
            • 18. Re: indexes in unusable state
              3530
              Hi,

              Query like this will help you.

              SELECT a.owner, a.table_name, a.partitioning_type,
              a.subpartitioning_type, a.partition_count,
              b.column_position, b.column_name
              FROM all_part_tables a, all_part_key_columns b
              WHERE a.owner= b.owner
              AND a.table_name = b.name
              and a.owner = '&towner'
              AND a.table_name = '&tname';

              Or if you generate DDL from tool like Toad or SQL Developer, you can check in there also.

              Dilip Patel.
              • 19. Re: indexes in unusable state
                586983
                Hi Dilip,


                Thanks for responding to my doubts..

                i tried to execute ur query:

                SQL> SELECT a.owner, a.table_name, a.partitioning_type,
                2 a.subpartitioning_type, a.partition_count,
                3 b.column_position, b.column_name
                4 FROM all_part_tables a, all_part_key_columns b
                5 WHERE a.owner= b.owner
                6 AND a.table_name = b.name
                7 and a.owner = '&towner'
                8 AND a.table_name = '&tname';
                Enter value for towner: casino
                old 7: and a.owner = '&towner'
                new 7: and a.owner = 'casino'
                Enter value for tname: NETWORKPLAYERGAMES
                old 8: AND a.table_name = '&tname'
                new 8: AND a.table_name = 'NETWORKPLAYERGAMES'

                no rows selected


                I m doing the rebuiling stuff daily...
                I really need to find out some permanent solution on this...

                Dilip, Can u pls explain me how can i use the update global indexes command? If possible u can give me the sql command...I am not much experienced, so need to know how it works.


                THanks in advance
                Arundhati
                • 20. Re: indexes in unusable state
                  586983
                  Hi,

                  how can i find out which is the DDL which is making that index unusable?

                  Thanks,
                  Arundhati
                  • 21. Re: indexes in unusable state
                    Randolf Geist
                    user583980 wrote:
                    Hi Dilip,


                    Thanks for responding to my doubts..

                    Enter value for towner: casino
                    old 7: and a.owner = '&towner'
                    new 7: and a.owner = 'casino'

                    no rows selected
                    You used a lower case name for the owner, but in the dictionary these are by default all uppercase. May be you want to try again using all uppercase.
                    Dilip, Can u pls explain me how can i use the update global indexes command? If possible u can give me the sql command...I am not much experienced, so need to know how it works.
                    Arundhati,

                    the UPDATE GLOBAL INDEXES command can be used as part of the ALTER TABLE partition maintenance command. Check the documentation, I've even mentioned it in one of my above posts.

                    In order to find out the statement that is causing the issues, there are several ways to accomplish this:

                    1. In the ALERT log you should get a message whenever an index gets UNUSABLE, so you have an approximate time information when this happens:
                    Timestamp:
                    Index <owner.index_name> or some [sub]partitions of the index have been marked unusable
                    2. In order to get more detailed information, I suggest you configure and turn on auditing on that particular object, e.g. something like:

                    a) Turn on auditing if not already done so
                    -- if you have an spfile, otherwise modify pfile
                    -- you need to bounce the instance to activate this
                    alter system set audit_trail = db scope = spfile; 
                    b) Enable object auditing
                    audit alter on <table_name>;
                    c) Check the audit trail
                    select * from dba_audit_trail;
                    d) Once you have the information you need, turn auditing off again
                    noaudit alter on <table_name>;
                    alter system set audit_trail = none scope = spfile; 
                    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/
                    • 22. Re: indexes in unusable state
                      3530
                      Hi Arundhati,

                      I could have written query like

                      SELECT a.owner, a.table_name, a.partitioning_type,
                      a.subpartitioning_type, a.partition_count,
                      b.column_position, b.column_name
                      FROM all_part_tables a, all_part_key_columns b
                      WHERE a.owner= b.owner
                      AND a.table_name = b.name
                      and a.owner = upper('&towner')
                      AND a.table_name = upper('&tname');

                      Or you can download SQL Developer and use to get all table structure details easily. (I am old fashion command line DBA ;-) ), To get details quickly you can use tool.

                      Randolf have given answer to your query, update here your findings.

                      Dilip Patel.

                      Edited by: Dilip Patel on Jan 15, 2009 12:02 PM
                      • 23. Re: indexes in unusable state
                        586983
                        Hi,

                        Pls find the o/p of the query:

                        SELECT a.owner, a.table_name, a.partitioning_type,
                        a.subpartitioning_type, a.partition_count,
                        b.column_position, b.column_name
                        FROM all_part_tables a, all_part_key_columns b
                        WHERE a.owner= b.owner
                        AND a.table_name = b.name
                        and a.owner = '&towner'
                        AND a.table_name = '&tname';



                        PARTITION_COUNT COLUMN_POSITION
                        --------------- ---------------
                        COLUMN_NAME
                        --------------------------------------------------------------------------------
                        CASINO NETWORKPLAYERGAMES LIST NONE
                        2 1
                        STATUS

                        1 row selected.


                        Thanks,
                        Arundhati
                        • 24. Re: indexes in unusable state
                          3530
                          Hi,

                          Have you tried audit on the table as per Randolf's advice.

                          Dilip Patel.
                          • 25. Re: indexes in unusable state
                            586983
                            HI Dilip and Randolf,


                            Thanks a lot for your help. I am able to find out the DDL, and the problem is solved.. :)

                            Arundhati
                            • 26. Re: indexes in unusable state
                              722682
                              Can you share how you find out the problem and how to resolve it??? Thanks!
                              • 27. Re: indexes in unusable state
                                108476
                                Hi Arundhati,
                                I m doing the rebuiling stuff daily... I am able to find out the DDL, and the problem is solved..
                                So, the daily index rebuilding helped?
                                I really need to find out some permanent solution on this...
                                Even though rebuilding is 100% safe when properly done, I would run check scripts and only rebuild indexes when it is likely that the rebuild will help you:

                                http://www.dba-oracle.com/t_index_rebuilding_issues.htm

                                Hope this helps . . .

                                Donald K. Burleson
                                Oracle Press author
                                Author of "Oracle Tuning: The Definitive Reference"
                                http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
                                "Time flies like an arrow; Fruit flies like a banana".
                                • 28. Re: indexes in unusable state
                                  Randolf Geist
                                  burleson wrote:
                                  So, the daily index rebuilding helped?
                                  No, if you read this (resurrected) thread carefully, it was not about the index rebuilds that should help, but it was about an index becoming unusable and the OP wanted to find out why it actually got in this state.

                                  So the thread was about how to avoid an index rebuild by finding out which DDL operation invalidated the index.

                                  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/
                                  • 29. Re: indexes in unusable state
                                    Randolf Geist
                                    user4274395 wrote:
                                    Can you share how you find out the problem and how to resolve it??? Thanks!
                                    Obviously the DDL that invalidated the index was identified by enabled auditing of the ALTER command on this particular table. Once the DDL was identified a corresponding change in processing possibly was applied that avoided this index invalidation.

                                    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/
                                    1 2 Previous Next