Как обойти сбойный блок в таблице : Oracle

execute dbms_repair.skip_corrupt_blocks('SUPERMAG', 'SMPOSTVIRTPACKS');
alter table supermag.smpostvirtpacks move;
execute dbms_repair.skip_corrupt_blocks('SUPERMAG', 'SMPOSTVIRTPACKS',flags=>dbms_repair.noskip_flag);

CREATE TABLE corrupt_block (file_id NUMBER, block_id NUMBER);

select * from v$database_block_corruption;

create SALVAGE_ROWS_TABLE as select * from SMPOSTVIRTPACKS where rownum<1;

Script
REM NAME: salvage.sql
REM NOTE: This program will not handle long, long raw colums or chained rows.
SET ECHO OFF;
SET SERVEROUTPUT ON;

CREATE OR REPLACE FUNCTION dectohex(a IN NUMBER) RETURN VARCHAR2 IS
x VARCHAR2(8) := '';
y VARCHAR2(1);
z NUMBER;
w NUMBER;
BEGIN
IF a > POWER(2,32) OR a < 0 THEN
RAISE invalid_number;
END IF;
w := a;
WHILE w > 0 LOOP
z := w MOD 16;
IF z = 10 THEN y := 'A';
ELSIF z = 11 THEN y := 'B';
ELSIF z = 12 THEN y := 'C';
ELSIF z = 13 THEN y := 'D';
ELSIF z = 14 THEN y := 'E';
ELSIF z = 15 THEN y := 'F';
ELSE y := TO_CHAR(z);
END IF;
w := TRUNC(w / 16);
x := CONCAT(y,x); — build x string backwards
END LOOP;
RETURN x;
END;
/

DECLARE

table_name VARCHAR2(30):= 'SMPOSTVIRTPACKS';
user_name VARCHAR2(30):= 'SUPERMAG';

file_id NUMBER;
block_id NUMBER;
extentid number;
blocks NUMBER;
file_id_hex VARCHAR2(4);
block_id_hex VARCHAR2(8);
row_count_hex VARCHAR2(4);
row_count NUMBER;
block_counter NUMBER;
for_loop_counter NUMBER;
corrupt_block_flag NUMBER:=0;
check_file NUMBER;
check_block NUMBER;
row_id_hex CHAR(18);
invalid_rowid EXCEPTION;
rows_per_block NUMBER:=100;

CURSOR c1 IS SELECT file_id, block_id, blocks,extent_id FROM dba_extents
WHERE SEGMENT_NAME = UPPER(table_name) AND OWNER = UPPER(user_name)
ORDER BY EXTENT_ID;

CURSOR c2 IS SELECT file_id,block_id FROM corrupt_block;

PROCEDURE ins (v_rowid VARCHAR2) is
bad_rowid EXCEPTION;
PRAGMA EXCEPTION_INIT (bad_rowid, -01410);
BEGIN
INSERT INTO salvaged_rows_table
SELECT *
FROM SUPERMAG.SMPOSTVIRTPACKS
WHERE rowid = v_rowid;

EXCEPTION
WHEN bad_rowid THEN
NULL;
END ins;

BEGIN
OPEN c1;
LOOP
FETCH c1 INTO file_id, block_id, blocks,extentid;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('extent: file:'||to_char(file_id)||'block_id:'||to_char(block_id)||' blocks:'||to_char(blocks));

file_id_hex := DECTOHEX(file_id);
block_counter:= block_id;
for_loop_counter:= block_id + blocks-1;
FOR i IN block_id..for_loop_counter LOOP
corrupt_block_flag:= 0;
OPEN c2;
LOOP
FETCH c2 INTO check_file, check_block;
EXIT WHEN c2%NOTFOUND;

IF (check_block = block_counter AND check_file = file_id) THEN
corrupt_block_flag :=1;

dbms_output.put_line('….Skipping corrupt block:'||to_char(block_counter)|| ' file id:'||to_char(file_id));

END IF;
END LOOP;
CLOSE c2;


block_id_hex := dectohex(block_counter);

IF corrupt_block_flag = 0 THEN

FOR row_count IN 0..rows_per_block LOOP
IF row_count = 0 THEN
row_count_hex := '0';
ELSE
row_count_hex:=dectohex(row_count);
END IF;
row_id_hex:=LPAD(block_id_hex,8,0) ||'.'||
LPAD(row_count_hex,4,0) ||'.'|| LPAD(file_id_hex,4,0);
ins(row_id_hex);
END LOOP;
END IF;
block_counter := block_counter+1;
COMMIT;

END LOOP;
END LOOP;
CLOSE c1;
COMMIT;
END;
/