通过rman做check数据文件后，查询出对象及类型：
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#, greatest(e.block_id, c.block#) corr_start_block#, least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#, least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) - greatest(e.block_id, c.block#) + 1 blocks_corrupted, null description FROM dba_extents e, v$database_block_corruption c WHERE e.file_id = c.file# AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#, header_block corr_start_block#, header_block corr_end_block#, 1 blocks_corrupted, 'Segment Header' description FROM dba_segments s, v$database_block_corruption c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#, greatest(f.block_id, c.block#) corr_start_block#, least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#, least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) - greatest(f.block_id, c.block#) + 1 blocks_corrupted, 'Free Block' description FROM dba_free_space f, v$database_block_corruption c WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block# order by file#, corr_start_block#;
查出结果
如果是表损坏，在添加event 10231跳过也不能导出的情况下，生成新表，根据rowid生成insert语句，正常数据插入到新表create table test as seelct * from BL_SHUJUBD t where 1=2;
select 'insert into test select * from BL_SHUJUBD t where rowid between '||''''||dbms_rowid.rowid_create(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID,0)||''''||' and '||''''||DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,10000)||''''||';'||CHR(10)||'commit;' from dba_extents e,dba_objects o
where e.segment_name='BL_SHUJUBD'
and e.owner='HIS4'
AND o.object_name = 'BL_SHUJUBD'
AND o.owner='HIS4';
然后重命名表
