Connected.
SQL>
SQL> -- Repair Table
SQL>
SQL> declare
2 begin
3 -- create repair table
4 dbms_repair.admin_tables (
5 -- table_name => 'REPAIR_TABLE',
6 table_type => dbms_repair.repair_table,
7 action => dbms_repair.create_action,
8 tablespace => 'USERS'); -- 如果是使用SYS用户的缺省表空间，该项就不用指定
9 end;
10 /
PL/SQL procedure successfully completed.
我们查询dba_objects，可以看到如下结果：
SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_name like '%REPAIR_TABLE';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------
SYS DBA_REPAIR_TABLE VIEW
SYS REPAIR_TABLE TABLE
SQL>
SQL> -- Orphan Key Table
SQL>
SQL> declare
2 begin
3 -- Create orphan key table
4 dbms_repair.admin_tables (
5 table_type => dbms_repair.orphan_table,
6 action => dbms_repair.create_action,
7 tablespace => 'USERS'); -- 如果是使用SYS用户的缺省表空间，该项就不用指定
8 end;
9 /
PL/SQL procedure successfully completed.
我们查询dba_objects，可以看到如下结果：
SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_name like '%ORPHAN_KEY_TABLE';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------
SYS DBA_ORPHAN_KEY_TABLE VIEW
SYS ORPHAN_KEY_TABLE TABLE
二、使用DBMS_REPAIR.CHECK_OBJECT进行检测
CHECK_OBJECT procedure检查指定的object，并且将关于损坏和修补的指导信息装入Repair Table。它将效验指定object中所有块的一致性。而在此之前已标识的块就会被跳过。
SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2 rpr_count int;
3 begin
4 rpr_count := 0;
5 dbms_repair.check_object (
6 schema_name => 'SYSTEM',
7 object_name => 'T1',
8 repair_table_name => 'REPAIR_TABLE',
9 corrupt_count => rpr_count);
10 dbms_output.put_line('repair count: ' || to_char(rpr_count));
11 end;
12 /
repair count: 1
PL/SQL procedure successfully completed.
repair_table的结构如下：
SQL> desc repair_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
我们可以从repair_table中查询坏块的情况：
SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2 corrupt_description, repair_description
3 from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1 3 1 FALSE
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
mark block software corrupt
三、从坏块中进行数据抽取
从repair_table中可以知道file 6的block 3 坏了，但注意此时这个块还没有被标识为坏块，因此要在这个时候将任何有意义的数据赶快抽取出来。一旦该块被标识为坏块，整个块就会被跳过。
1、 通过ALTER SYSTEM DUMP或trace中来获取块中包含的记录数 (nrows = 3).
2、 查询损坏的object，尽量抽取尽可能多的信息。
