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):= 'dept';
user_name VARCHAR2(30):= 'scott';
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 SCOTT.DEPT
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;
/
