This discussion is archived
14 Replies Latest reply: Jun 20, 2010 7:06 PM by CKPT RSS

how to check unusable index

739618 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    STATUS column from DBA_INDEXES, DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS.

    Nicolas.
  • 2. Re: how to check unusable index
    rajeysh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    HI

    sol is to recreate them or rebuilt
  • 11. Re: how to check unusable index
    rajeysh Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    For partition chech Dba_ind_partition and for sub partition chech Dba ind subpartition index.
  • 14. Re: how to check unusable index
    CKPT Guru
    Currently Being Moderated
    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

Legend

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