skip to main content

SAP NetWeaver Newbie

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#;

No comments:

Post a Comment

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.