oracle数据坏块处理(三)-数据抽取插入到新表中
通过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 descriptionFROM dba_extents e, v$database_block_corruption cWHERE e.file_id = c.file#AND e.block_id <= c.block# + c.blocks - 1AND 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' descriptionFROM dba_segments s, v$database_block_corruption cWHERE 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' descriptionFROM dba_free_space f, v$database_block_corruption cWHERE f.file_id = c.file#AND f.block_id <= c.block# + c.blocks - 1AND 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';
然后重命名表