Finding Corrupt Indexes on Oracle

You can find the corrupted INDEXES via ORACLE database view “v$database_block_corruption”..Let’s check with an example…

Connect to the ORACLE database via SQLPlus ;

C:\> sqlplus / as sysdba

Finding Corrupt Indexes on Oracle

Then we can format the output with following commands ;

set pagesize 50 linesize 170
col segment_name format a30
col partition_name format a30

Finding Corrupt Indexes on Oracle

Now we can use the following sql syntax to get corrupted data

select distinct file#,segment_name,segment_type, TABLESPACE_NAME, partition_name from dba_extents a,v$database_block_corruption b where a.file_id=b.file# and a.BLOCK_ID <= b.BLOCK# and a.BLOCK_ID + a.BLOCKS  >= b.BLOCK#;

Finding Corrupt Indexes on Oracle

As a result , we can get the corruption information…

 

Leave a Reply


nine + 6 =

Blogroll