有时候为了考虑数据库的I/O性能，我们可能会考虑把索引表空间和数据表空间分开存放。索引被删除后，影响的仅仅是数据库的性能（原来走索引的执行计划，只能走全表扫描了），但不会使数据库丢数据，因此，只要重建索引即可，问题不大。那么问题来了，如果忘记了之前创建索引用到的语句，该怎么恢复索引呢？而且是在没有RMAN备份集、热备份的情况下
--创建索引表空间
SQL> create tablespace indx datafile '/u01/app/oracle/oradata/ora10g/indx01.dbf' size 50m;
Tablespace created.
--在emp表上创建2个索引
SQL> create index ind_no on emp(empno) tablespace indx;
Index created.
SQL> create index ind_name on emp(ename) tablespace indx;
Index created.
SQL> col segment_name for a15SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='INDX';
SEGMENT_NAME SEGMENT_TYPE OWNER--------------- ------------------ ------------------------------IND_NO INDEX ZLMIND_NAME INDEX ZLM
--破坏表空间数据文件
SQL> !
[oracle@ora10g ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/indx01.dbf[oracle@ora10g ~]$ cat /u01/app/oracle/oradata/ora10g/indx01.dbfabcefghij
--提取元数据SQL> set long 10000
SQL> select dbms_metadata.get_ddl('INDEX','IND_NO','ZLM') from dual;
DBMS_METADATA.GET_DDL('INDEX','IND_NO','ZLM')--------------------------------------------------------------------------------
CREATE INDEX "ZLM"."IND_NO" ON "ZLM"."EMP" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX"
SQL> select dbms_metadata.get_ddl('INDEX','IND_NAME','ZLM') from dual;
DBMS_METADATA.GET_DDL('INDEX','IND_NAME','ZLM')--------------------------------------------------------------------------------
CREATE INDEX "ZLM"."IND_NAME" ON "ZLM"."EMP" ("ENAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX"
注意，虽然文件坏了，但仍然能找到元数据，因为元数据存在于 system 表空间的字典中，而没有存在 indx表空间
--删除损坏的索引表空间
SQL> drop tablespace indx including contents and datafiles;drop tablespace indx including contents and datafiles*ERROR at line 1:ORA-01122: database file 7 failed verification checkORA-01110: data file 7: '/u01/app/oracle/oradata/ora10g/indx01.dbf'ORA-01251: Unknown File Header Version read for file number 7
由于文件还是online状态，不能直接删除表空间，先把文件offline
SQL> alter database datafile 7 offline;
Database altered.
SQL> drop tablespace indx including contents and datafiles;
Tablespace dropped.
--重建索引表空间
SQL> create tablespace indx2 datafile '/u01/app/oracle/oradata/ora10g/indx02.dbf' size 20m;
Tablespace created.
--重建之前的索引
注意：一定要先提取元数据再删除损坏的表空间，因为表空间删除以后，元数据也被删除了
SQL> CREATE INDEX "ZLM"."IND_NO" ON "ZLM"."EMP" ("EMPNO") 2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 5 TABLESPACE "INDX2";
Index created.
SQL> CREATE INDEX "ZLM"."IND_NAME" ON "ZLM"."EMP" ("ENAME") 2 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 5 TABLESPACE "INDX2";
Index created.
这里重新创建的索引表空间为INDX2
SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='INDX2';
SEGMENT_NAME SEGMENT_TYPE OWNER--------------- ------------------ ------------------------------IND_NO INDEX ZLMIND_NAME INDEX ZLM
--查看数据库中的表空间和数据文件
SQL> select file_name,tablespace_name,bytes/1024/1024 as "size(M)" from dba_data_files;
FILE_NAME TABLESPACE_NAME size(M)--------------------------------------------- --------------- ----------/u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM 50/u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE 100/u01/app/oracle/oradata/ora10g/users01.dbf USERS 40/u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX 270/u01/app/oracle/oradata/ora10g/undotbs01.dbf UNDOTBS1 165/u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM 560/u01/app/oracle/oradata/ora10g/indx02.dbf INDX2 20
7 rows selected.
由于保留了之前索引的元数据，我们从数据字典中获取了元数据，然后再新建的索引表空间中重建索引，这是恢复索引表空间的另一种思路，当然，如果在有RMAN备份集、热备份的情况下，也可以直接对datafile 7进行还原和恢复操作，可以参考之前的博客内容