以下命令使用sys用户执行
1. 创建修复表DBMS_REPAIR.ADMIN_TABLES
SQL> BEGIN
2 DBMS_REPAIR.ADMIN_TABLES (
3 TABLE_NAME => 'REPAIR_TABLE',
4 TABLE_TYPE => dbms_repair.repair_table,
5 ACTION => dbms_repair.create_action,
6 TABLESPACE => '&tablespace_name');
7 END;
8 /
PL/SQL procedure successfully completed
2. 确定坏块BMS_REPAIR.CHECK_OBJECT
SQL>
SQL> set serveroutput on
SQL> DECLARE num_corrupt INT;
2 BEGIN
3 num_corrupt := 0;
4 DBMS_REPAIR.CHECK_OBJECT (
5 SCHEMA_NAME => '&schema_name',
6 OBJECT_NAME => '&object_name',
7 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
8 corrupt_count => num_corrupt);
9 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
10 END;
11 /
number corrupt: 1
PL/SQL procedure successfully completed
3. 查询检测到的坏块
SQL>
SQL> select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
2 from REPAIR_TABLE;
BLOCK_ID CORRUPT_TYPE CORRUPT_DESCRIPTION
---------- ------------ --------------------------------------------------------------------------------
992620 6148
4. 标记坏块DBMS_REPAIR.FIX_CORRUPT_BLOCKS
SQL>
SQL> DECLARE num_fix INT;
2 BEGIN
3 num_fix := 0;
4 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
5 SCHEMA_NAME => '&schema_name',
6 OBJECT_NAME=> '&object_name',
7 OBJECT_TYPE => dbms_repair.table_object,
8 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
9 FIX_COUNT=> num_fix);
10 DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
11 END;
12 /
num fix: 0
PL/SQL procedure successfully completed
5. 设置DML跳过坏块DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
SQL>
SQL> BEGIN
2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
3 SCHEMA_NAME => '&schema_name',
4 OBJECT_NAME => '&object_name',
5 OBJECT_TYPE => dbms_repair.table_object,
6 FLAGS => dbms_repair.SKIP_FLAG);
7 END;
8 /
PL/SQL procedure successfully completed
SQL>
6. 注意事项:
1 使用DBMS_REPAIR跳过坏块后,重启索引如果需要访问这些坏块将报错,如果有唯一约束，插入重复数据将报ORA-1错误
2 如果想取消坏块掉过可以使用dbms_repair.NOSKIP_FLAG值
3 使用SKIP_CORRUPT_BLOCKS,后如果想从表清楚坏块数据,可以使用alter table
4 CHECK_OBJECT过程将在段头获取一个 LOCK (TM) in MODE=3 Row-X (SX)锁这将阻塞其他进程获取DDL或者独占TM锁