Finding Corrupt Indexes on Oracle Database

You can find out all the indexes that are corrupted by matching up v$database_block_corruption view.
Let's begin with formatting the output:
set pagesize 50 linesize 170
col segment_name format a30
col partition_name format a30

Now the following SQL will list out the corrupt indexes:
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#;

Comments

Popular posts from this blog

OS/DB Migration - CMD. STR, TOC, EXT, R3load, DDLDBS.TPL and more

Fixing Inconsistent Table - Table activation fails due to inconsistency between DD and DB

301 Redirect Using SAP Web Dispatcher