ORACEL坏查询对象批量脚本

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:ORACEL坏查询对象批量脚本

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

查询坏块

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# + c.blocks - 1
 10     AND e.block_id + e.blocks - 1 >= 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# + c.blocks - 1
 30     AND f.block_id + f.blocks - 1 >= 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