index 表空间损坏，如何在线rebuild

来源：互联网发布：如何投诉淘宝店铺编辑：程序博客网时间：2024/04/19 15:42

SQL> select segment_name ,segment_type from user_segments where TABLESPACE_NAME='INDEX_TS';

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
EMP_IDX
INDEX

EMP_PK
INDEX

DEPT_PK
INDEX

SQL> select * from emp where empno='123';
select * from emp where empno='123'
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/oradata/index_ts01.ora'

SQL> create tablespace index_ts_temp datafile '/u01/oradata/index_ts_temp01.ora' size 128m;
connect
Tablespace created.

SQL> drop tablespace index_ts INCLUDING CONTENTS;
drop tablespace index_ts INCLUDING CONTENTS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter index DEPT_PK rebuild tablespace index_ts_temp;
alter index DEPT_PK rebuild tablespace index_ts_temp
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/oradata/index_ts01.ora'

因为不是在线rebuild ，所以会读取原来index 的数据

SQL> alter index DEPT_PK rebuild tablespace index_ts_temp online;

Index altered.

加上online 表示不读取原来index 的数据，直接从table 里面读取数据重新建立 index

http://rchsh.itpub.net/post/8010/61333