Batch El bad query object script

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.