See RMAN effective use
Given a block, retrieve row pk
DECLARE
CURSOR c
IS
SELECT rowid, snap_id
FROM perfstat.STATS$SYSSTAT
where snap_id = 12722 and name ='logons cumulative';
rc c%ROWTYPE;
mrowid_type NUMBER;
mobject_number NUMBER;
mrelative_fno NUMBER;
mblock_number NUMBER;
mrow_number NUMBER;
currrow NUMBER := 0;
lastcurrrowout NUMBER := 0;
feedback NUMBER := 50000;
BEGIN
OPEN c;
FETCH c
INTO rc;
LOOP
EXIT WHEN c%NOTFOUND;
DBMS_ROWID.rowid_info (rowid_in => rc.ROWID,
rowid_type => mrowid_type,
object_number => mobject_number,
relative_fno => mrelative_fno,
block_number => mblock_number,
ROW_NUMBER => mrow_number
);
currrow := currrow + 1;
-- USE ON CORRUPTED BLOCKS
--IF ((mrelative_fno = 840) AND (mblock_number = 479391))
--THEN
SYS.DBMS_SYSTEM.ksdwrt (1, 'DBA ' || mrelative_fno || ',' || mblock_number || ' for pk ' || rc.snap_id);
--END IF;
IF (currrow - lastcurrrowout > feedback)
THEN
lastcurrrowout := currrow;
SYS.DBMS_SYSTEM.ksdwrt (1, ' feedback ' || mblock_number);
END IF;
FETCH c
INTO rc;
END LOOP;
CLOSE c;
EXCEPTION
WHEN OTHERS
THEN
SYS.DBMS_SYSTEM.ksdwrt (1, 'Exception on poid_id0 ' || rc.snap_id);
RAISE;
END; |
dbms_repair usage
Make tbs REPAIR_TBS 512 Mb
-- Create table REPAIR_TABLE on new tbs
exec dbms_repair.admin_tables('REPAIR_TABLE',1,1,'REPAIR_TBS');
--Launch check
set serveroutput on
declare corr_count binary_integer;
begin
corr_count := 0;
dbms_repair.CHECK_OBJECT (
schema_name => 'PIN',
object_name => 'ITEM_T',
partition_name => null,
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
flags => null,
relative_fno => null,
block_start => null,
block_end => null,
corrupt_count => corr_count
);
dbms_output.put_line(to_char(corr_count));
end;
/
|