1.创建测试表空间ts_idx
SYS@PROD1>create tablespace ts_idx datafile '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf' size 10m
2 autoextend on;
Tablespace created.
2.创建测试表,创建索引且使用ts_idx
SCOTT@PROD1>create table t1(x int) tablespace users;
Table created.
SCOTT@PROD1>insert into t1 select rownum from dual connect by rownum<=10;
10 rows created.
SCOTT@PROD1>commit;
Commit complete.
SCOTT@PROD1>alter table t1 add constraint pk_t1 primary key(x) using index tablespace ts_idx;
Table altered.
3.验证索引表空间已使用(status字段表示非分区索引是否失效或无法使用)
SCOTT@PROD1>select INDEX_NAME,STATUS,TABLESPACE_NAME from user_indexes where table_name='T1';
INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 VALID TS_IDX
4.删除ts_idx表空间的数据文件
SCOTT@PROD1>! rm /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf
SCOTT@PROD1>! ls /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf
ls: /u01/app/oracle/oradata/PROD1/ts_idx_01.dbf: No such file or directory
5.此时数据库已无法一致性关闭,只能强制关闭
SYS@PROD1>shutdown immediate;
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@PROD1>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@PROD1>shutdown abort;
ORACLE instance shut down.
6.将数据库启动到mount模式下
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 528485456 bytes
Database Buffers 301989888 bytes
Redo Buffers 2371584 bytes
Database mounted.
7.将丢失的数据文件offline
SYS@PROD1>alter database datafile 8 offline;
Database altered.
8.打开数据库
SYS@PROD1>alter database open;
Database altered.
9.查看索引的状态,显然这个状态是不对的
SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';
INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 VALID TS_IDX
10.创建一个新的表空间,用于存放索引,替代丢失了数据文件的ts_idx表空间
SYS@PROD1>create tablespace ts_idx_2 datafile '/u01/app/oracle/oradata/PROD1/ts_idx02.dbf' size 10m
2 autoextend on;
Tablespace created.
11.将索引PK_T1重建到ts_idx_1上
SYS@PROD1>alter index scott.pk_t1 rebuild tablespace ts_idx_2;
alter index scott.pk_t1 rebuild tablespace ts_idx_2
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/ts_idx_01.dbf'
在重建时,会对丢失的表空间进行读操作
12.只能将索引手动改为不可用状态
SYS@PROD1>alter index scott.PK_T1 unusable;
Index altered.
SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';
INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 UNUSABLE TS_IDX
13.再次rebuild到新的表空间
SYS@PROD1>alter index scott.pk_t1 rebuild tablespace ts_idx_2;
Index altered.
SYS@PROD1>select index_name,status,tablespace_name from dba_indexes where owner='SCOTT' and table_name='T1';
INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
PK_T1 VALID TS_IDX_2
14.删除丢失数据文件的ts_idx表空间
SYS@PROD1>drop tablespace ts_idx;
Tablespace dropped.
注如果数据量比较大,创建索引会是非常漫长的一个过程,通过PARALLEL和NOLOGGING可以减少创建时间