ORACEL坏查询对象批量脚本

查询坏块 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 120
SQL> 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 2000
SQL>          set linesize 250
SQL>      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坏查询对象批量脚本, 感谢原作者分享。