Oracle rebuild index的偶然发现

今天在做测试中一次偶然发现，详见以下测试过程

引用

SQL> create index t_idx1 on t(dataobj#) tablespace indexs;

Index created.

SQL> alter tablespace indexs offline;

Tablespace altered.

SQL> insert into t select * from t where rownum=1;
insert into t select * from t where rownum=1
            *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/opt/ora11g/oradata/db11g/index01.dbf'



SQL> alter index t_idx1 rebuild;
alter index t_idx1 rebuild
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/opt/ora11g/oradata/db11g/index01.dbf'


SQL> alter index t_idx1 rebuild tablespace users;
alter index t_idx1 rebuild tablespace users
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/opt/ora11g/oradata/db11g/index01.dbf'

SQL> select INDEX_NAME,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
T_IDX1                          VALID
SQL> drop index t_idx1;

Index dropped.

SQL> create index t_idx1 on t(dataobj#);

Index created.

由以上可以看出，Oracle rebuild索引的数据源是原索引，即参考原来的索引进行rebuild，而drop再create则不然。顺便提一句，
1、如果将表格用move方式整理碎片，索引将失效，所以需要将索引重建。

引用

SQL> alter table t move;

Table altered.

SQL> select INDEX_NAME,status,tablespace_name from user_indexes;

INDEX_NAME                     STATUS   TABLESPACE_NAME
------------------------------ -------- ------------------------------
T_IDX                          UNUSABLE USERS
T_IDX1                         UNUSABLE INDEXS

2、如果将索引表空间删除，索引也讲随之删除

引用

SQL> drop tablespace indexs including contents and datafiles;

Tablespace dropped.

SQL> select INDEX_NAME,status,tablespace_name from user_indexes;

INDEX_NAME                     STATUS   TABLESPACE_NAME
------------------------------ -------- ------------------------------
T_IDX                          UNUSABLE USERS