Some indexes or index partitions of table have been marked unusable

SQL> show parameter index

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ITPUB

optimizer_index_caching integer 0

optimizer_index_cost_adj integer 100
skip_unusable_indexes boolean TRUE

看看是否是10g中存在了一個類似的初

始化引數，使得索引在不可用狀態下，基表同樣可以進行DML操作。

查詢果然發現了這個引數，而且從名稱以及設定的值來看，確認就是要找的初始化引數：

SQL> alter session set skip_unusable_indexes = false;

[@more@]

Some indexes or index partitions of table have been marked unusable

公司中的新人問起索引失效對錶影響，於是隨手做了個例子，才發現10g中已經改變了9i中的預設方式：

SQL> create table t_index (id number, name varchar2(30));

Table created.

SQL> create index ind_t_id on t_index(id);

Index created.

SQL> create index ind_t_name on t_index(name);

Index created.

SQL> insert into t_index values (1, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t_index move;

Table altered.

SQL> select index_name, status from user_indexes where table_name = 'T_INDEX';

INDEX_NAME STATUS
------------------------------ --------
IND_T_ID UNUSABLEI
IND_T_NAME UNUSABLE

SQL> insert into t_index values (2, 'b');

1 row created.

當這個插入語句成功後，我發現自己做的例子居然和預期產生了差異，在我的印象中，這個插入語句是應該報

錯的。

隨後馬上做了三件事情：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select index_name, status from user_indexes where table_name = 'T_INDEX';

INDEX_NAME STATUS
------------------------------ --------
IND_T_ID UNUSABLE
IND_T_NAME UNUSABLE

SQL> show parameter index

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
skip_unusable_indexes boolean TRUE

首先第一件事情就是堅持當前資料庫的版本，我可以確定在9i，最後一個INSERT會由於索引的狀態不可用而導

致錯誤，而當前可以執行成功，因此首先確定版本問題。

在確認是10.2版本後，隨後又堅持了一下索引的狀態，看看是否插入導致Oracle重建了索引。而當前索引的狀

態並未發生變化，說明Oracle在執行插入時，跳過了這些不可用的索引。

於是順理成章的就是第三個動作，堅持包含index關鍵字的初始化引數，看看是否是10g中存在了一個類似的初

始化引數，使得索引在不可用狀態下，基表同樣可以進行DML操作。

查詢果然發現了這個引數，而且從名稱以及設定的值來看，確認就是要找的初始化引數：

SQL> alter session set skip_unusable_indexes = false;

Session altered.

SQL> insert into t_index values (3, 'c');
insert into t_index values (3, 'c')
ORA-01502: index 'TEST.IND_T_ID' or partition of such index is in unusable state

SQL> alter index ind_t_id rebuild;

Index altered.

SQL> alter index ind_t_name rebuild;

Index altered.

SQL> insert into t_index values (3, 'c');

1 row created.

SQL> commit;

Commit complete.

將初始化引數設定為FALSE後，資料庫的行為果然表現的和9i中一致。

查詢了一下Oracle的文件，發現在10.1中Oracle已經引入了這個初始化引數。這個引數的引入可以避免由於表

MOVE或其他DDL導致的索引失效，從而影響到整個表的可用性，同時這個引數的引入也會存在問題，它使得本來

可以很快發現的問題埋藏的很深，甚至可能造成嚴重的效能隱患。

當然Oracle也意識到這一點，在告警日誌中可以看到這些的提示資訊：

Sat Dec 31 10:53:33 2011
Some indexes or index [sub]partitions of table TEST.T_INDEX have been marked unusable

[TEST1@orcl#05-9月 -10] SQL>create index ind_test on test1(a);

索引已建立。

[TEST1@orcl#05-9月 -10] SQL>select tablespace_name FROM user_segments where

segment_name='IND_TEST';

TABLESPACE_NAME
------------------------------
TEST

[TEST1@orcl#05-9月 -10] SQL>alter index ind_test rebuild;

索引已更改。

[TEST1@orcl#05-9月 -10] SQL>select tablespace_name FROM user_segments where

segment_name='IND_TEST';

TABLESPACE_NAME
------------------------------
TEST

現在換到SYS使用者下：
[SYS@orcl#05-9月 -10] SQL>alter index test1.ind_test rebuild;

索引已更改。
[SYS@orcl#05-9月 -10] SQL>select tablespace_name FROM dba_segments where segment_name='IND_TEST';

TABLESPACE_NAME
------------------------------
TEST
說明重建索引是預設表空間。

[SYS@orcl#05-9月 -10] SQL>alter index test1.ind_test rebuild tablespace users;

索引已更改。
[TEST1@orcl#05-9月 -10] SQL>select tablespace_name FROM user_segments where

segment_name='IND_TEST';

TABLESPACE_NAME
------------------------------
USERS

說明可以在另一個表空間重建。

alter index rebuild與alter index rebuild online的區別
alter index rebuild online實質上是掃描表而不是掃描現有的索引塊來實現索引的重建alter index rebuild

只掃描現有的索引塊來實現索引的重建。

方法三：
獲取索引的DDL語句，重建索引
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

create index