3 Replies Latest reply: Sep 3, 2009 7:08 PM by Hemant K Chitale RSS

    Index status is "N/A"

    Raman
      Hi,

      I can see around 55 indexes built on sys/system tables are in N/A state. how do we re-built?

      SQL> select distinct(status) from dba_indexes;

      N/A
      VALID

      SQL> select status,index_name,owner,table_name from dba_indexes where status not like 'VALID';

      N/A WRH$_SERVICE_WAIT_CLASS_PK SYS
      WRH$_SERVICE_WAIT_CLASS

      N/A WRH$_SERVICE_STAT_PK SYS
      WRH$_SERVICE_STAT

      N/A WRH$_TABLESPACE_STAT_PK SYS
      WRH$_TABLESPACE_STAT

      N/A WRH$_ACTIVE_SESSION_HISTORY_PK SYS
      WRH$_ACTIVE_SESSION_HISTORY

      N/A WRH$_INST_CACHE_TRANSFER_PK SYS

      WRH$_INST_CACHE_TRANSFER

      N/A WRH$_DLM_MISC_PK SYS
      WRH$_DLM_MISC

      N/A WRH$_SEG_STAT_PK SYS
      WRH$_SEG_STAT

      N/A WRH$_PARAMETER_PK SYS
      WRH$_PARAMETER

      N/A WRH$_OSSTAT_PK SYS
      WRH$_OSSTAT


      N/A WRH$_SYS_TIME_MODEL_PK SYS
      WRH$_SYS_TIME_MODEL

      N/A WRH$_SYSSTAT_PK SYS
      WRH$_SYSSTAT

      N/A WRH$_SGASTAT_U SYS
      WRH$_SGASTAT

      N/A WRH$_ROWCACHE_SUMMARY_PK SYS
      WRH$_ROWCACHE_SUMMARY


      N/A WRH$_DB_CACHE_ADVICE_PK SYS
      WRH$_DB_CACHE_ADVICE

      N/A WRH$_LATCH_MISSES_SUMMARY_PK SYS
      WRH$_LATCH_MISSES_SUMMARY

      N/A WRH$_LATCH_PARENT_PK SYS
      WRH$_LATCH_PARENT

      N/A WRH$_LATCH_CHILDREN_PK SYS
      WRH$_LATCH_CHILDREN

      N/A WRH$_LATCH_PK SYS

      WRH$_LATCH

      N/A WRH$_WAITSTAT_PK SYS
      WRH$_WAITSTAT

      N/A WRH$_SYSTEM_EVENT_PK SYS
      WRH$_SYSTEM_EVENT

      N/A WRH$_SQLSTAT_PK SYS
      WRH$_SQLSTAT

      N/A WRH$_SQLSTAT_INDEX SYS
      WRH$_SQLSTAT


      N/A WRH$_FILESTATXS_PK SYS
      WRH$_FILESTATXS

      N/A I_STREAMS_APPLY_SPILL_MSGS_PT1 SYS
      STREAMS$_APPLY_SPILL_MSGS_PART

      N/A SYS_IL0000008174C00008$$ SYS
      STREAMS$_APPLY_SPILL_MSGS_PART

      N/A LOGMNR_I1INDCOMPART$ SYSTEM
      LOGMNR_INDCOMPART$


      N/A LOGMNR_I1INDSUBPART$ SYSTEM
      LOGMNR_INDSUBPART$

      N/A LOGMNR_I2INDPART$ SYSTEM
      LOGMNR_INDPART$

      N/A LOGMNR_I1INDPART$ SYSTEM
      LOGMNR_INDPART$

      N/A LOGMNR_I1LOBFRAG$ SYSTEM
      LOGMNR_LOBFRAG$

      N/A LOGMNR_I1ICOL$ SYSTEM

      LOGMNR_ICOL$

      N/A LOGMNR_I1CCOL$ SYSTEM
      LOGMNR_CCOL$

      N/A LOGMNR_I1CDEF$ SYSTEM
      LOGMNR_CDEF$

      N/A LOGMNR_I1LOB$ SYSTEM
      LOGMNR_LOB$

      N/A LOGMNR_I1COLTYPE$ SYSTEM
      LOGMNR_COLTYPE$


      N/A LOGMNR_I1TYPE$ SYSTEM
      LOGMNR_TYPE$

      N/A LOGMNR_I2TABCOMPART$ SYSTEM
      LOGMNR_TABCOMPART$

      N/A LOGMNR_I1TABCOMPART$ SYSTEM
      LOGMNR_TABCOMPART$

      N/A LOGMNR_I2TABSUBPART$ SYSTEM
      LOGMNR_TABSUBPART$


      N/A LOGMNR_I1TABSUBPART$ SYSTEM
      LOGMNR_TABSUBPART$

      N/A LOGMNR_I2TABPART$ SYSTEM
      LOGMNR_TABPART$

      N/A LOGMNR_I1TABPART$ SYSTEM
      LOGMNR_TABPART$

      N/A LOGMNR_I2IND$ SYSTEM
      LOGMNR_IND$

      N/A LOGMNR_I1IND$ SYSTEM

      LOGMNR_IND$

      N/A LOGMNR_I1TS$ SYSTEM
      LOGMNR_TS$

      N/A LOGMNR_I1ATTRCOL$ SYSTEM
      LOGMNR_ATTRCOL$

      N/A LOGMNR_I2COL$ SYSTEM
      LOGMNR_COL$

      N/A LOGMNR_I1COL$ SYSTEM
      LOGMNR_COL$


      N/A LOGMNR_I1TAB$ SYSTEM
      LOGMNR_TAB$

      N/A LOGMNR_I2TAB$ SYSTEM
      LOGMNR_TAB$

      N/A LOGMNRC_GSII_PK SYSTEM
      LOGMNRC_GSII

      N/A LOGMNRC_GTCS_PK SYSTEM
      LOGMNRC_GTCS


      N/A LOGMNRC_GTLO_PK SYSTEM
      LOGMNRC_GTLO

      N/A LOGMNRC_I3GTLO SYSTEM
      LOGMNRC_GTLO

      N/A LOGMNRC_I2GTLO SYSTEM
      LOGMNRC_GTLO

      N/A LOGMNR_I1USER$ SYSTEM
      LOGMNR_USER$

      N/A LOGMNR_I1OBJ$ SYSTEM

      LOGMNR_OBJ$


      57 rows selected.
        • 1. Re: Index status is "N/A"
          sb92075
          partitioned indexes?
          SQL> desc dba_ind_partitions
           Name                            Null?    Type
           ----------------------------------------- -------- ----------------------------
           INDEX_OWNER                             VARCHAR2(30)
           INDEX_NAME                             VARCHAR2(30)
           COMPOSITE                             VARCHAR2(3)
           PARTITION_NAME                         VARCHAR2(30)
           SUBPARTITION_COUNT                        NUMBER
           HIGH_VALUE                             LONG
           HIGH_VALUE_LENGTH                        NUMBER
           PARTITION_POSITION                        NUMBER
           STATUS                              VARCHAR2(8)
           TABLESPACE_NAME                        VARCHAR2(30)
           PCT_FREE                             NUMBER
           INI_TRANS                             NUMBER
           MAX_TRANS                             NUMBER
           INITIAL_EXTENT                         NUMBER
           NEXT_EXTENT                             NUMBER
           MIN_EXTENT                             NUMBER
           MAX_EXTENT                             NUMBER
           PCT_INCREASE                             NUMBER
           FREELISTS                             NUMBER
           FREELIST_GROUPS                        NUMBER
           LOGGING                             VARCHAR2(7)
           COMPRESSION                             VARCHAR2(8)
           BLEVEL                              NUMBER
           LEAF_BLOCKS                             NUMBER
           DISTINCT_KEYS                             NUMBER
           AVG_LEAF_BLOCKS_PER_KEY                   NUMBER
           AVG_DATA_BLOCKS_PER_KEY                   NUMBER
           CLUSTERING_FACTOR                        NUMBER
           NUM_ROWS                             NUMBER
           SAMPLE_SIZE                             NUMBER
           LAST_ANALYZED                             DATE
           BUFFER_POOL                             VARCHAR2(7)
           USER_STATS                             VARCHAR2(3)
           PCT_DIRECT_ACCESS                        NUMBER
           GLOBAL_STATS                             VARCHAR2(3)
           DOMIDX_OPSTATUS                        VARCHAR2(6)
           PARAMETERS                             VARCHAR2(1000)
          • 2. Re: Index status is "N/A"
            Hans Forbrich
            sb92075 wrote:
            partitioned indexes?
            partitioned indexes! (see metalink note 445158.1)
            • 3. Re: Index status is "N/A"
              Hemant K Chitale
              Since they are Partitioned Indexes, you should look for their Partitions in DBA_IND_PARTITIONS.

              Hemant K Chitale

              Note : See thread how to rebuilt partioned table index

              Edited by: Hemant K Chitale on Sep 4, 2009 8:07 AM