查询坏块 sql set lines 120sql select * from v$database_block_corruption; file# block# blocks corruption_change# corruptio ---------- ---------- ---------- ------------------ --------- 3 35418 1 0 fractured 3 61344 1 0 fractured 3 31065 1 0
查询坏块
sql> set lines 120sql> select * from v$database_block_corruption; file# block# blocks corruption_change# corruptio ---------- ---------- ---------- ------------------ --------- 3 35418 1 0 fractured 3 61344 1 0 fractured 3 31065 1 0 corrupt 3 36673 1 0 corrupt 3 36721 1 0 corrupt 3 42881 1 0 corrupt 1 66738 1 0 corrupt 3 36329 1 0 corrupt 3 36617 1 0 corrupt 3 32404 1 0 fractured 3 36281 1 0 fractured file# block# blocks corruption_change# corruptio ---------- ---------- ---------- ------------------ --------- 3 36625 1 0 fractured 1 39041 1 0 corrupt 3 36713 1 0 corrupt 10 69927 1 0 fractured 26 94244 1 0 corrupt 已选择16行。
查询坏块对应对象
sql> set pagesize 2000sql> set linesize 250sql> select e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# 2 , greatest(e.block_id, c.block#) corr_start_block# 3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# 4 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 5 - greatest(e.block_id, c.block#) + 1 blocks_corrupted 6 , null description 7 from dba_extents e, v$database_block_corruption c 8 where e.file_id = c.file# 9 and e.block_id = c.block# 11 union 12 select s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# 13 , header_block corr_start_block# 14 , header_block corr_end_block# 15 , 1 blocks_corrupted 16 , 'segment header' description 17 from dba_segments s, v$database_block_corruption c 18 where s.header_file = c.file# 19 and s.header_block between c.block# and c.block# + c.blocks - 1 20 union 21 select null owner, null segment_type, null segment_name, null partition_name, c.file# 22 , greatest(f.block_id, c.block#) corr_start_block# 23 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# 24 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 25 - greatest(f.block_id, c.block#) + 1 blocks_corrupted 26 , 'free block' description 27 from dba_free_space f, v$database_block_corruption c 28 where f.file_id = c.file# 29 and f.block_id = c.block# 31 order by file#, corr_start_block#;owner segment_type segment_name partition_name file# corr_start_block# corr_end_block# blocks_corrupted description---------------- ----------------- ----------------------- ------------------------------- ------ ----------------- --------------- ---------------- -------------- sys table obj$ 1 39041 39041 1 1 66738 66738 1 free block sysman index mgmt_metrics_1hour_pk 3 31065 31065 1 sys table wrh$_sql_bind_metadata 3 32404 32404 1 sys table wrh$_bg_event_summary 3 35418 35418 1 sys index partition wrh$_filestatxs_pk wrh$_filest_1232289473_41482 3 36281 36281 1 sys table partition wrh$_system_event wrh$_system_1232289473_41482 3 36329 36329 1 sys table partition wrh$_sgastat wrh$_sgasta_1232289473_41482 3 36617 36617 1 sys index partition wrh$_sgastat_u wrh$_sgasta_1232289473_41482 3 36625 36625 1 sys index partition wrh$_parameter_pk wrh$_parame_1232289473_41482 3 36673 36673 1 sys table partition wrh$_service_stat wrh$_servic_1232289473_41482 3 36713 36713 1 sys index partition wrh$_service_stat_pk wrh$_servic_1232289473_41482 3 36721 36721 1 sys table partition wrh$_latch wrh$_latch_1232289473_41482 3 42881 42881 1 sys table wri$_adv_actions 3 61344 61344 1 exam table exam_items_old 10 69927 69927 1 cpr table need_monitor 26 94244 94244 1 已选择16行。 sql> sql> spool off
假坏块引起恐慌记录一次ora-00600[kdsgrp1]分析read by other session等待事件table中各种坏块对select/dml操作影响shell脚本获得extents分布数据库报ora-00607/ora-00600[4194]错误记录一次ora-600[13013]处理过程latch: cache buffers chains 原文地址：oracel坏查询对象批量脚本, 感谢原作者分享。