14 Replies Latest reply: Jun 20, 2010 9:06 PM by CKPT RSS

    how to check unusable index

    739618
      Hi all
      I am getiing error
      Index ORVETL.NU_1_761 or some [sub]partitions of the index have been marked unusable

      How to check index which is unusable (Partition , non partition ALL)

      Pl help me
        • 1. Re: how to check unusable index
          Nicolas.Gasparotto
          STATUS column from DBA_INDEXES, DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS.

          Nicolas.
          • 2. Re: how to check unusable index
            rajeysh
            SQL> select owner, index_name from dba_indexes where status='UNUSABLE';
            refer:

            http://arjudba.blogspot.com/2008/04/troubleshoot-unusable-index-in-oracle.html

            hope, this will be helpful
            • 3. Re: how to check unusable index
              739618
              from this query i did not get unusable index
              which is given in alert log file
              SQL> select owner, index_name from dba_indexes where status='UNUSABLE';

              OWNER INDEX_NAME
              ------------------------------ ------------------------------
              ORVETL IND_CDR_BUCK_SLOT_MSC_RCF
              ORVETL IND_SUBS_TYPE_KEY_RCF
              ORVETL IND_ROAM_EVENT_TYP_FLG_RCF
              ORVETL IND_EVENT_DIRECTION_KEY_RCF
              ORVETL IND_EVENT_DATER050
              ORVETL SRC_S_K_1
              ORVETL C_B_I_1
              ORVETL IND_SERV_MSRN_ID_RCF
              ORVETL IND_SUBS_KEY_571
              ORVETL IND_EVNT__TYP_KEY_571
              ORVETL IND_EVNT_DIR_KEY_571
              ORVETL IND_CDR_BUCK_TMSLT_571
              ORVETL IND_CDR_BUCK_KEY_571
              ORVETL IND_SUBS_CIRCLE_ID_KEY_REJ2
              ORVETL IND_SRC_SYSTEM_KEY_REJ2
              ORVETL IND_ROAM_EVNT_FLG_REJ2
              ORVETL IND_NETW_ELEM_ID_KEY_REJ2
              ORVETL IND_EVNT_DIR_KEY_REJ2
              ORVETL IND_CDR_BUCK_SLOT_REJ2
              ORVETL IND_CDR_BUCKET_ID_KEY_REJ2
              ORVETL IND_EVENT_TYP_KEY_REJ2
              ORVETL RPT_EXCP_PK_OUT_2_751
              ORVETL RPT_EXCP_PK_OUT_1_751
              ORVETL RPT_EXCP_PK_OUT_2_752
              ORVETL RPT_EXCP_PK_OUT_1_752
              ORVETL RPT_EXCP_PK_OUT_2_749
              ORVETL RPT_EXCP_PK_OUT_1_749
              ORVETL RPT_EXCP_PK_OUT_1_750
              ORVETL RPT_EXCP_PK_OUT_2_750
              ORVETL RPT_EXCP_PK_OUT_2_885
              ORVETL RPT_EXCP_PK_OUT_1_885
              ORVETL RPT_EXCP_PK_OUT_1_879
              ORVETL RPT_EXCP_PK_OUT_2_879
              ORVETL RPT_EXCP_PK_OUT_2_841
              ORVETL RPT_EXCP_PK_OUT_1_841
              ORVETL RPT_EXCP_PK_OUT_1_703
              ORVETL RPT_EXCP_PK_OUT_2_703
              ORVETL RPT_EXCP_PK_OUT_2_755
              ORVETL RPT_EXCP_PK_OUT_1_755
              ORVETL RPT_EXCP_PK_OUT_1_757
              ORVETL RPT_EXCP_PK_OUT_2_757
              ORVETL RPT_EXCP_PK_OUT_2_761
              ORVETL RPT_EXCP_PK_OUT_1_761
              ORVETL RPT_EXCP_PK_OUT_1_762
              ORVETL RPT_EXCP_PK_OUT_2_760
              ORVETL RPT_EXCP_PK_OUT_1_760
              ORVETL RPT_EXCP_PK_OUT_2_762
              ORVETL RPT_EXCP_PK_OUT_1_741
              ORVETL RPT_EXCP_PK_OUT_2_741
              ORVETL IND_R071_BUCK_EVENTS
              ORVETL IND_TRANS_IN
              ORVETL IND_TRANSACTION_ID
              ORVETL IDX_CIRC_NM
              ORVETL IDX_RA_MAST_CI_CIRCLE_SHORT_UK
              ORVETL PKDIM_RA_MASTER_CIRCLE
              ORVETL IDX_RA_MAST_CI_CIRCLE_SHORT_NA
              ORVETL IDX__ELEMENT_SRC_CIRCLE_ID_KEY
              ORVETL IDX_EMENT_NETWORK_ELEMENT_NAME
              ORVETL DIM_SWT_IN_BYPASS_CONFIG_PK
              ORVETL IND_CDR_BUCK_SLOT_REJ_IN
              ORVETL IND_SUBS_CIRCLE_ID_REJ_IN
              ORVETL IND_ROAM_EVN_TYP_FLG_BSCS
              ORVETL IND_CDR_BUC_ID_KEY_BSCS
              ORVETL IND_CDR_BUCK_SLOT_RCF
              ORVETL IND_SUBS_CIRCLE_ID_RCF
              ORVETL IND_ROAM_EVNT_TYP_FLG_RCF
              ORVETL IND_TEST_EVENT_FLG_RCF
              ORVETL IND_CDR_BUCKET_ID_KEY_RCF
              ORVETL IND_NTWRK_ELID_116A
              ORVETL IND_CDR_BUCK_SLOT_MSISDN_1
              ORVETL IND_CDR_BUCKET_ID_MSISDN_1
              ORVETL IND_SUBS_CIRCLE_ID_MSISDN_1
              ORVETL IND_CDR_BUCK_SLOT_MSISDNS1
              ORVETL IND_CDR_BUCKET_ID_MSISDNS1
              ORVETL IND_SUBS_CIRCLE_ID_MSISDNS1
              ORVETL IND_ROAMING_EVENT_KEY_MSISDN
              ORVETL IND_EVENT_DIRECTION_KEY_MSISDN
              ORVETL IND_CDR_BUCK_SLOT_SLOT
              ORVETL IND_CDR_BUCKET_ID_MSC_SHORT
              ORVETL IND_SUBS_CIRCLE_ID_MSC_SHORT
              ORVETL IND_MSC_R_E_T_F
              ORVETL IND_MSC_S_T_K
              ORVETL INDX_CIR_ID_KEY
              • 4. Re: how to check unusable index
                739618
                Hi all

                i got status

                status is unusable so no one using that index i am rt

                So can i drop these unuseable index directly.
                • 5. Re: how to check unusable index
                  rajeysh
                  post your sql scripts using code tag

                  post your alert log errors also

                  Edited by: rajeysh on Jun 20, 2010 6:47 PM
                  • 6. Re: how to check unusable index
                    739618
                    I dont know the query it is user running when i got i will update

                    alert log plz

                    ORACLE Instance IDEARADB - Can not allocate log, archival required
                    Sun Jun 20 13:54:03 2010
                    Thread 1 cannot allocate new log, sequence 44150
                    All online logs needed archiving
                    Current log# 2 seq# 44149 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 13:54:08 2010
                    Thread 1 advanced to log sequence 44150 (LGWR switch)
                    Current log# 1 seq# 44150 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 13:56:47 2010
                    Thread 1 advanced to log sequence 44151 (LGWR switch)
                    Current log# 3 seq# 44151 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:00:34 2010
                    Thread 1 advanced to log sequence 44152 (LGWR switch)
                    Current log# 2 seq# 44152 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:06:55 2010
                    Thread 1 advanced to log sequence 44153 (LGWR switch)
                    Current log# 1 seq# 44153 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 14:09:31 2010
                    Thread 1 advanced to log sequence 44154 (LGWR switch)
                    Current log# 3 seq# 44154 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:12:07 2010
                    Thread 1 advanced to log sequence 44155 (LGWR switch)
                    Current log# 2 seq# 44155 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:14:30 2010
                    Thread 1 advanced to log sequence 44156 (LGWR switch)
                    Current log# 1 seq# 44156 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 14:17:09 2010
                    Thread 1 advanced to log sequence 44157 (LGWR switch)
                    Current log# 3 seq# 44157 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:19:42 2010
                    Thread 1 advanced to log sequence 44158 (LGWR switch)
                    Current log# 2 seq# 44158 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:22:19 2010
                    Thread 1 advanced to log sequence 44159 (LGWR switch)
                    Current log# 1 seq# 44159 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 14:24:45 2010
                    Thread 1 advanced to log sequence 44160 (LGWR switch)
                    Current log# 3 seq# 44160 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:27:15 2010
                    Thread 1 advanced to log sequence 44161 (LGWR switch)
                    Current log# 2 seq# 44161 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:29:45 2010
                    Thread 1 advanced to log sequence 44162 (LGWR switch)
                    Current log# 1 seq# 44162 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 14:32:21 2010
                    Thread 1 advanced to log sequence 44163 (LGWR switch)
                    Current log# 3 seq# 44163 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:34:58 2010
                    Thread 1 advanced to log sequence 44164 (LGWR switch)
                    Current log# 2 seq# 44164 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:37:35 2010
                    Thread 1 advanced to log sequence 44165 (LGWR switch)
                    Current log# 1 seq# 44165 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 14:40:08 2010
                    Thread 1 advanced to log sequence 44166 (LGWR switch)
                    Current log# 3 seq# 44166 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:43:52 2010
                    Thread 1 advanced to log sequence 44167 (LGWR switch)
                    Current log# 2 seq# 44167 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:50:46 2010
                    Thread 1 advanced to log sequence 44168 (LGWR switch)
                    Current log# 1 seq# 44168 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 14:51:47 2010
                    Thread 1 advanced to log sequence 44169 (LGWR switch)
                    Current log# 3 seq# 44169 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 14:53:05 2010
                    Thread 1 advanced to log sequence 44170 (LGWR switch)
                    Current log# 2 seq# 44170 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 14:56:59 2010
                    Thread 1 advanced to log sequence 44171 (LGWR switch)
                    Current log# 1 seq# 44171 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 15:07:42 2010
                    Thread 1 advanced to log sequence 44172 (LGWR switch)
                    Current log# 3 seq# 44172 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 15:17:31 2010
                    Thread 1 advanced to log sequence 44173 (LGWR switch)
                    Current log# 2 seq# 44173 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 15:24:32 2010
                    Thread 1 advanced to log sequence 44174 (LGWR switch)
                    Current log# 1 seq# 44174 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 15:32:49 2010
                    Thread 1 advanced to log sequence 44175 (LGWR switch)
                    Current log# 3 seq# 44175 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 15:41:28 2010
                    Thread 1 advanced to log sequence 44176 (LGWR switch)
                    Current log# 2 seq# 44176 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 15:45:28 2010
                    Index ORVETL.NU_1_761 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 15:45:49 2010
                    Index ORVETL.NU_2_761 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 15:48:24 2010
                    Index ORVETL.NU_1_762 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 15:49:03 2010
                    Index ORVETL.NU_2_762 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 15:51:11 2010
                    Thread 1 advanced to log sequence 44177 (LGWR switch)
                    Current log# 1 seq# 44177 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 16:01:10 2010
                    Thread 1 advanced to log sequence 44178 (LGWR switch)
                    Current log# 3 seq# 44178 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 16:06:20 2010
                    Index ORVETL.NU_1_751 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 16:11:04 2010
                    Thread 1 advanced to log sequence 44179 (LGWR switch)
                    Current log# 2 seq# 44179 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 16:16:40 2010
                    Index ORVETL.NU_1_753 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 16:17:54 2010
                    Index ORVETL.NU_2_753 or some [sub]partitions of the index have been marked unusable
                    Sun Jun 20 16:20:28 2010
                    Thread 1 advanced to log sequence 44180 (LGWR switch)
                    Current log# 1 seq# 44180 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 16:30:11 2010
                    Thread 1 advanced to log sequence 44181 (LGWR switch)
                    Current log# 3 seq# 44181 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 16:38:45 2010
                    Thread 1 advanced to log sequence 44182 (LGWR switch)
                    Current log# 2 seq# 44182 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 16:40:40 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 54067K exceeds notification threshold (51200K)
                    Sun Jun 20 16:41:08 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 54016K exceeds notification threshold (51200K)
                    Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1081756.trc
                    Sun Jun 20 16:41:17 2010
                    Thread 1 advanced to log sequence 44183 (LGWR switch)
                    Current log# 1 seq# 44183 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 16:41:32 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 54066K exceeds notification threshold (51200K)
                    Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1081756.trc
                    Sun Jun 20 16:41:56 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 54015K exceeds notification threshold (51200K)
                    Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1081756.trc
                    Sun Jun 20 16:49:45 2010
                    Thread 1 advanced to log sequence 44184 (LGWR switch)
                    Current log# 3 seq# 44184 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 16:58:01 2010
                    Thread 1 advanced to log sequence 44185 (LGWR switch)
                    Current log# 2 seq# 44185 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 17:00:15 2010
                    Thread 1 advanced to log sequence 44186 (LGWR switch)
                    Current log# 1 seq# 44186 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 17:02:37 2010
                    Thread 1 advanced to log sequence 44187 (LGWR switch)
                    Current log# 3 seq# 44187 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 17:05:13 2010
                    Thread 1 advanced to log sequence 44188 (LGWR switch)
                    Current log# 2 seq# 44188 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 17:07:37 2010
                    Thread 1 advanced to log sequence 44189 (LGWR switch)
                    Current log# 1 seq# 44189 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 17:13:36 2010
                    Thread 1 advanced to log sequence 44190 (LGWR switch)
                    Current log# 3 seq# 44190 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 17:19:16 2010
                    Thread 1 advanced to log sequence 44191 (LGWR switch)
                    Current log# 2 seq# 44191 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 17:25:15 2010
                    Thread 1 advanced to log sequence 44192 (LGWR switch)
                    Current log# 1 seq# 44192 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 17:32:18 2010
                    Thread 1 advanced to log sequence 44193 (LGWR switch)
                    Current log# 3 seq# 44193 mem# 0: +REDO_LOG/redo03.log
                    Sun Jun 20 17:37:41 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 53686K exceeds notification threshold (51200K)
                    Sun Jun 20 17:38:02 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 53898K exceeds notification threshold (51200K)
                    Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1241490.trc
                    Sun Jun 20 17:38:21 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 54025K exceeds notification threshold (51200K)
                    Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1241490.trc
                    Sun Jun 20 17:38:40 2010
                    Memory Notification: Library Cache Object loaded into SGA
                    Heap size 54012K exceeds notification threshold (51200K)
                    Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1241490.trc
                    Sun Jun 20 17:39:21 2010
                    Thread 1 advanced to log sequence 44194 (LGWR switch)
                    Current log# 2 seq# 44194 mem# 0: +REDO_LOG/redo02.log
                    Sun Jun 20 17:45:28 2010
                    Thread 1 advanced to log sequence 44195 (LGWR switch)
                    Current log# 1 seq# 44195 mem# 0: +REDO_LOG/redo01.log
                    Sun Jun 20 17:51:21 2010
                    • 7. Re: how to check unusable index
                      rajeysh
                      what is your oracle version?
                      please post the oracle version.
                      select * from v$version;

                      refer this, this will helps you.
                      http://www.dba-oracle.com/oracle_tips_unused_indexes.htm

                      Edited by: rajeysh on Jun 20, 2010 6:55 PM
                      • 8. Re: how to check unusable index
                        Nicolas.Gasparotto
                        Don't drop them, if the indexes are marked as unusable, that's maybe because the tables have been moved, or exchange partition or so. It is not the same as they were not in used at all.
                        You would want to rebuild them before hands.

                        Nicolas.
                        • 9. Re: how to check unusable index
                          rajeysh
                          user8862191 wrote:
                          Hi all

                          i got status

                          status is unusable so no one using that index i am rt

                          So can i drop these unuseable index directly.
                          refer this link: follow the steps, dont drop the index.
                          indexes in unusable state

                          Edited by: rajeysh on Jun 20, 2010 7:09 PM
                          • 10. Re: how to check unusable index
                            739618
                            HI

                            sol is to recreate them or rebuilt
                            • 11. Re: how to check unusable index
                              rajeysh
                              Details in trace file /oracle/oracle/Oracle_10gr2_DB/admin/IDEARADB/udump/idearadb_ora_1241490.trc

                              post the information in the above trace file.
                              • 12. Re: how to check unusable index
                                561093
                                Try to read about the following before coming to any conclusion:

                                1) Why makes indexes UNUSABLE
                                2) What happens when indexes are in UNUSABLE state
                                3) What to do when index is in UNUSABLE state

                                I could find the following:

                                http://www.databasejournal.com/features/oracle/article.php/3735286/Oracle-Unusable-Indexes.htm

                                http://www.oracledba.co.uk/tips/truncate_unusable.htm

                                http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_1008.htm
                                • 13. Re: how to check unusable index
                                  Taral
                                  For partition chech Dba_ind_partition and for sub partition chech Dba ind subpartition index.
                                  • 14. Re: how to check unusable index
                                    CKPT
                                    hi,

                                    you can check the unusable indexes even if partioned or not.


                                    select owner,table_name,index_name,last_analyzed,status from dba_indexes where status *!=* 'VALID';


                                    hope this will give you clear