今天收到系统ALERT REPORT报某个数据库有INDEX STATUS 为UNUSABLE.
马上登陆数据库从DBA_INDEXES查询相关索引状态.结果发现那几个INDEX的STATUS为N/A,以前从来没有碰到过
这个.到METALINK 上搜了一把,找到了解释.记录一下,方便自己查询.

Subject: WHY IS STATUS COLUMN OF DBA_INDEXES, USER_INDEXES, ALL_INDEXES = N/A
Doc ID: Note:1039614.6 Type: BULLETIN
Last Revision Date: 12-APR-2001 Status: PUBLISHED
Why is the Status Column of DBA_INDEXES, USER_INDEXES or ALL_INDEXES = ‘N/A’?

You are using Oracle8 and are selecting from the Data Dictionary views
DBA_INDEXES, USER_INDEXES, or ALL_INDEXES and you see that the status of all
partitioned indexes show as ‘N/A’. What does this mean?
The status column of DBA_INDEXES, USER_INDEXES and ALL_INDEXES is used to
indicate whether an index is in the direct load state. In Oracle7 the status
column can have a value of VALID or DIRECT LOAD. In Oracle8 the status column
can have a value of VALID, UNUSABLE or N/A. (UNUSABLE in Oracle8 is the
equivalent of DIRECT LOAD in Oracle7).
If the status of an index shows as N/A in DBA_INDEXES, USER_INDEXES, or
ALL_INDEXES, this indicates that this is a partitioned index and that this
field is not applicable. To determine the status of a partitioned index you
should check the Data Dictionary views DBA_IND_PARTITIONS,
USER_IND_PARTITIONS, or ALL_IND_PARTITIONS.

For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

    SELECT partition_name,
             STATUS
      FROM DBA_IND_PARTITIONS
      WHERE INDEX_NAME = '<index_name>';
 
   Here IS a specific example:
---------------------------
 
 CREATE TABLE orders (
                         order_no      number,
                         part_no       varchar2(40),
                         ord_date      date)
  partition BY range (ord_date)
  (partition Q1 VALUES less than (TO_DATE('01-APR-1999','DD-MON-YYYY'))
   tablespace DATA,
   partition Q2 VALUES less than (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
   tablespace DATA,
   partition Q3 VALUES less than (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
   tablespace DATA,
   partition Q4 VALUES less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
   tablespace DATA);
 
Statement processed.
 
CREATE bitmap INDEX orders_local_2_idx
    ON orders(part_no)
       LOCAL
      (partition LOCAL1
       tablespace USERS,
       partition LOCAL2
       tablespace USERS,
       partition LOCAL3
       tablespace USERS,
       partition LOCAL4
       tablespace USERS);
 
Statement processed.
 
SELECT index_name, STATUS FROM dba_indexes WHERE index_name LIKE
 
('%LOCAL%');
 
INDEX_NAME                     STATUS
------------------------------ --------
ORDERS_LOCAL_2_IDX             N/A
1 row selected.
 
SELECT index_name,partition_name, STATUS FROM user_ind_partitions;
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ORDERS_LOCAL_2_IDX             LOCAL1                         USABLE
ORDERS_LOCAL_2_IDX             LOCAL2                         USABLE
ORDERS_LOCAL_2_IDX             LOCAL3                         USABLE
ORDERS_LOCAL_2_IDX             LOCAL4                         USABLE
 
4 rows selected.

DBA_IND_PARTITIONS displays the statistics for each partition in the
index. DBA_INDEXES only gives a high level view of the index. Each
partition can have its own status so you need to query DBA_IND_PARTITIONS.

References:
===========
Oracle8 Reference Manual

Popularity: 26% [?]