Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
| 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
FROM dba_extents
WHERE file_id = 13
GROUP BY owner, segment_name)
SELECT *
FROM q
WHERE mbid > 100001; |
| Having identied the segments the next step is to move them with DBMS_REDEFINITION (no outage) or ALTER TABLE MOVE TABLESPACE. |
|
