Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


Index status is "N/A"


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.

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) 

sb92075 wrote: partitioned indexes?  partitioned indexes!  (see metalink note 445158.1)

Since they are Partitioned Indexes, you should look for their Partitions in DBA_IND_PARTITIONS.  Hemant K Chitale  Note : See thread http://forums.oracle.com/forums/thread.jspa?threadID=953057&tstart=0  Edited by: Hemant K Chitale on Sep 4, 2009 8:07 AM


Related Links

Ordering a partitioned table with global index
Determining which queries are failing from the database side
EXCEL TO ORACLE FORM
data type not supported
oracle-xe: Error 5 initializing SQL*Plus - NLS initialization error
ORA-01115: IO error reading block
Remote connectivity behind a firewall
High CPU load
Starting Listener from Remote Machine
ADDM and performance analysis problem
I have a Question
Error importing data: ORA-00910: specified length too long for its datatype
Bulk Loading from flat file to the Oracle database?
Database creation having a trace of the control file
HELP! with re-creating lost datafile without Backup.
returning form a pl/sql procedure.