索引表空间损坏的恢复

取元数据

select dbms_metadata.get_ddl('类型','对象','模式')

取出scott用户下所有表的元数据

select(select dbms_metadata.get_ddl('TABLE',table_name,owner) from dual) fromdba_tables where owner='SCOTT';

取出scott用户下所有所有的元数据

select(select dbms_metadata.get_ddl('INDEX',index_name,owner) from dual) fromdba_indexes where owner='SCOTT';

索引表空间损坏恢复的实验

SQL>create tablespace ind_tbs datafile '/u01/tiger/oradata/orcl2/ind_tbs01.dbf'size 10m;

Tablespacecreated.

SQL> create index i1 on emp1(ename) tablespace ind_tbs;

Index created.

SQL> create index i2 on emp1(sal)tablespace ind_tbs;

Index created.

SQL> selectsegment_type,segment_name,owner from dba_segments wheretablespace_name='IND_TBS';

SEGMENT_TYPE       SEGMENT_NAME         OWNER

------------------ --------------------------------------------------

INDEX              I1                   SCOTT

INDEX              I2                   SCOTT

SQL> !cp /etc/passwd/u01/tiger/oradata/orcl2/ind_tbs01.dbf

SQL> select * from emp1 wheresal<1000;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

     7369 SMITH      CLERK           7902 17-DEC-80        800                    20

     7900 JAMES      CLERK           7698 03-DEC-81        950                    30

SQL> alter system switch logfile;

System altered.

SQL> select * from emp1 wheresal<1000;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

     7369 SMITH      CLERK           7902 17-DEC-80        800                    20

     7900 JAMES      CLERK           7698 03-DEC-81        950                    30

SQL> altersystem checkpoint;

System altered.

SQL> select * from emp1 where sal<1000;

select * from emp1 where sal<1000

*

ERROR at line 1:

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5:'/u01/tiger/oradata/orcl2/ind_tbs01.dbf'

看一下五号文件在哪个表空间

SQL> select tablespace_name,file_id from dba_data_files where file_id=5;

TABLESPACE_NAME                   FILE_ID

------------------------------ ----------

IND_TBS                                 5

查看一下这个表空间里存放的是什么内容

SQL> select segment_type,segment_name,owner fromdba_segments where tablespace_name='IND_TBS';

SEGMENT_TYPE       SEGMENT_NAME         OWNER

------------------ --------------------------------------------------

INDEX              I1                   SCOTT

INDEX              I2                   SCOTT

获取元数据

SQL> selectdbms_metadata.get_ddl(segment_type,segment_name,owner) ||';' from dba_segmentswhere tablespace_name='IND_TBS';

删除坏了的索引表空间

SQL> drop tablespace ind_tbs includingcontents;

Tablespace dropped.

SQL> select name from v$datafile;

NAME

----------------------------------------

/u01/tiger/oradata/orcl2/system01.dbf

/u01/tiger/oradata/orcl2/undotbs01.dbf

/u01/tiger/oradata/orcl2/sysaux01.dbf

/u01/tiger/oradata/orcl2/users01.dbf

SQL> create tablespace ind_tbs datafile'/u01/tiger/oradata/orcl2/ind_tbs01.dbf' size 10m reuse;

Tablespace created.

 检查看索引是否可以使用了

SQL> select * from emp1 wheresal<1000;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

     7369 SMITH      CLERK           7902 17-DEC-80        800                    20

     7900 JAMES      CLERK           7698 03-DEC-81        950                    30