Find Segments Wholly Located Above Datafile Corruption
 
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.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved