Query Bad blocks SQLsetlines120SQLselect * fromv $ database_block_corruption; FILE # BLOCK # BLOCKSCORRUPTION_CHANGE # CORRUPTIO---------------------------------------------------------33541810FRACTURED36134410FRACTURED33106510
Query bad SQL set lines 120SQL select * from v $ database_block_partition uption; FILE # BLOCK # BLOCKS upload uption_change # upload uptio ---------- ------------------ --------- 3 35418 1 0 FRACTURED 3 61344 1 0 FRACTURED 3 31065 1 0
Query Bad blocks
SQL> set lines 120SQL> select * from v $ database_block_corruption; FILE # BLOCK # BLOCKS corrcorruption_change # CORRUPTIO ---------- others ----------- 3 35418 1 0 FRACTURED 3 61344 1 0 FRACTURED 3 31065 1 0 others upt 3 36673 1 36721 1 0 others upt 3 42881 1 0 slave upt 1 66738 1 0 slave upt 3 36329 1 0 slave upt 3 36617 1 0 slave upt 3 32404 1 0 FRACTURED 3 36281 1 0 FRACTU Red file # BLOCK # BLOCKS upload uption_change # CORRUPTIO ---------- ------------ then ----------- 3 36625 1 0 FRACTURED 1 39041 1 0 reply upt 3 36713 1 0 reply upt 10 69927 1 0 FRACTURED 26 94244 1 0 reply upt already exists select 16 rows.
Query the corresponding objects of Bad blocks
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 #) performance_start_block #3, least (e. block_id + e. blocks-1, c. block # + c. blocks-1) pai_end_block #4, least (e. block_id + e. blocks-1, c. block # + c. blocks-1) 5-greatest (e. block_id, c. block #) + 1 blocks_incluupted 6, null description 7 FROM dba_extents e, v $ d Atabase_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 pai_start_block #14, header_block pai_end_block #15, 1 blocks_upted 16, 'segment header' description 17 FROM dba_segments s, v $ database_block_c Orruption 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 #) performance_start_block #23, least (f. block_id + f. blocks-1, c. block # + c. blocks-1) pai_end_block #24, least (f. block_id + f. blocks-1, c. block # + c. blocks-1) 25-great Est (f. block_id, c. block #) + 1 blocks_0000upted 26, 'free Block' description 27 FROM dba_free_space f, v $ database_block_0000uption 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 #, pai_start_block #; OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE # pai_start_block # pai_end_block # blocks_upted DESCRIP TION ---------------- begin transaction ------ begin -------------------------- ---------------- sys table obj $1 39041 39041 1 1 66738 66738 1 Free Block sysman index Limit 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 $ _ F ILESTATXS_PK WRH $ _ PARTITION 3 36281 36281 1 sys table partition wrh $ _ SYSTEM_EVENT WRH $ _ SYSTEM_1232289473_41482 3 36329 36329 1 sys table partition wrh $ _ sgastat wrh $ _ PARTITION 3 36617 1 SYS index partition wrh $ _ SGASTAT_U WRH $ _ PARTITION 3 36625 36625 1 sys index partition wrh $ _ PARAMETER_PK WRH $ _ PARAME_1232289473_41482 3 36673 36673 1 SYS TABLE PARTITIO N wrh $ _ SERVICE_STAT WRH $ _ SERVIC_1232289473_41482 3 36713 36713 1 sys index partition wrh $ _ SERVICE_STAT_PK WRH $ _ PARTITION 3 36721 36721 1 sys table partition wrh $ _ latch wrh $ _ PARTITION 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 rows selected. SQL> spool off
- Fake And Bad blocks cause panic
- Record one ORA-00600 [kdsgrp1] Analysis
- Read by other session wait event
- Impact of Various Bad blocks in table on select/dml operations
- Use shell scripts to obtain the extents Distribution
- Database reports ORA-00607/ORA-00600 [4194] Error
- Record one ORA-600 [13013] Process
- Latch: cache buffers chains
Original article address: batch script of bad query objects in El. Thank you for sharing it with the original author.