今天收到系统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
thats for sure, guy
Interesting text.., bro