How Can I?
|A solution using DBMS_CRYPTO and Instead-Of Triggers
|From time to time checking v$database_block_corruption will reveal corrupt blocks. You will try to
recover them using BLOCK RECOVER but it may not work so the next step in the process is see if you can identify objects wholly
located at block numbers higher than the corruption, move them, then resize the datafile to be smaller than the corrupt location.
The following SQL will identify those objects, in a datafile, located completely above the location of a corrupt block.
This example assumes the corruption is in the SYSTEM tablespace, datafile 13, and the corruption is located in block 10,001.
|WITH q AS (
SELECT owner, segment_name, MIN(block_id) mbid
WHERE file_id = 13
GROUP BY owner, segment_name)
WHERE mbid > 100001;
|Having identied the segments the next step is to move them with DBMS_REDEFINITION (no outage) or ALTER TABLE MOVE TABLESPACE.