James's Blog

Oracle: how to skip bad block

First thing you need to do is find the bad block ids. To do this you can run dbverify. Lets assume our block id is 1234 .

Now find the segment name by running this query
select segment_name, segment_type, block_id, blocks from dba_extents where (1234 between block_id and (block_id + blocks - 1))

Once you have the bad segment name, its better to create a temporary table with good data if the segment is a table. If the segment is an index, drop the index and recreate it.
create table good_table as select * from bad_table where rowid not in ( select /*+index(bad_table, any_index)*/ rowid from bad_table where substr( rowid, 1, 8 ) = 1234)

posted by James Pei @ 4:10 PM