原博文：

http://blog.chinaunix.net/uid-77311-id-3051382.html

使用Dbms_Repair跳過壞塊

步驟1:表tb_test中有壞塊(模擬壞塊同方法1)

SQL> select count(1) from hxl.tb_test;

select count(1) from hxl.tb_test

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 12)

ORA-01110: data file 5: '/u01/app/oracle/oradata/oracl/hxl01.dbf'

步驟2:創建 REPAIR_TABLE 表Declare

Begin

-- create repair table

Dbms_Repair.Admin_Tables(Table_Name => 'REPAIR_TABLE',

Table_Type => Dbms_Repair.Repair_Table,

Action???? => Dbms_Repair.Create_Action,

Tablespace => 'SYSTEM');

End;

步驟3:創建 ORPHAN_KEY_TABLEDeclare

Begin

-- Create orphan key table

Dbms_Repair.Admin_Tables(Table_Type => Dbms_Repair.Orphan_Table,

Action???? => Dbms_Repair.Create_Action,

Tablespace => 'SYSTEM');

End;

步驟4:找出壞塊執行過程Check_Object后會將關于損壞和修補的指導信息裝入Repair Table.

Declare

Rpr_Count Int;

Begin

Rpr_Count := 0;

Dbms_Repair.Check_Object(Schema_Name?????? => 'HXL',

Object_Name?????? => 'TB_TEST',

Repair_Table_Name => 'REPAIR_TABLE',

Corrupt_Count???? => Rpr_Count);

Dbms_Output.Put_Line('repair count: ' || To_Char(Rpr_Count));

End;

該過程執行完成后,壞塊的信息會加載到repair_table表中.SQL> select object_id,tablespace_id,relative_file_id,block_id from repair_table;

OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID?? BLOCK_ID

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

51663???????????? 6??????????????? 5???????? 12

步驟5:修正壞塊FIX_CORRUPT_BLOCKS procedure用來根據repair table中的信息修正指定objects中的壞塊.

當這個塊被標識為壞了以后,做全表掃描將引起ORA-1578.

Declare

Fix_Count Int;

Begin

Fix_Count := 0;

Dbms_Repair.Fix_Corrupt_Blocks(Schema_Name?????? => 'HXL',

Object_Name?????? => 'TB_TEST',

Object_Type?????? => Dbms_Repair.Table_Object,

Repair_Table_Name => 'REPAIR_TABLE',

Fix_Count???????? => Fix_Count);

Dbms_Output.Put_Line('fix count: ' || To_Char(Fix_Count));

End;

步驟6:找出壞塊中記錄的index entries(因為該測試表TB_TEST沒有任何索引,該步驟跳過)

-- DUMP_ORPHAN_KEYS將會顯示指向數據壞塊中記錄的index entries

Declare

Key_Count Int;

Begin

Key_Count := 0;

Dbms_Repair.Dump_Orphan_Keys(Schema_Name?????? => 'HXL',

Object_Name?????? => 'TB_A_PK',

Object_Type?????? => Dbms_Repair.Index_Object,

Repair_Table_Name => 'REPAIR_TABLE',

Orphan_Table_Name => 'ORPHAN_KEY_TABLE',

Key_Count???????? => Key_Count);

Dbms_Output.Put_Line('orphan key count: ' || To_Char(Key_Count));

End;

步驟7:跳過壞塊-- 使用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS來跳過壞塊

Declare

Begin

Dbms_Repair.Skip_Corrupt_Blocks(Schema_Name => 'HXL',

Object_Name => 'TB_TEST',

Object_Type => Dbms_Repair.Table_Object,

Flags?????? => Dbms_Repair.Skip_Flag);

End;

過程執行完成后,可以全掃描該表.SQL> select count(1) from hxl.tb_test;

COUNT(1)

----------

1568

備注:Dbms_Repair包只能標記壞塊,但不能真正修復壞塊.