@TOC 上一篇写的是table中各种类型block坏块是否能被跳过,本篇关于table如果出现坏块,对select/dml操作影响。接上个测似表的数据，来进行测试

适用范围

oracle 所有版本

测试环境准备

SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS 
  2  WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';
SQL> set pages 1000 lines 1000 
SQL> col SEGMENT_NAME for a10
SQL> /

SEGMENT_NA HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
---------- ----------- ------------ ---------- ----------
ZHUO                 5         1410       1280         25

SQL> select  
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  2    3  max(dbms_rowid.rowid_block_number(rowid)) max_block,
  4  min(dbms_rowid.rowid_block_number(rowid)) min_block
  5  from zhuo.zhuo
  6  group by dbms_rowid.rowid_relative_fno(rowid);

   REL_FNO  MAX_BLOCK  MIN_BLOCK
---------- ---------- ----------
         5       2581       1411

SQL>  select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='ZHUO'
  2   AND SEGMENT_NAME='ZHUO';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          5       1408          8
         1          5       1416          8
         2          5       1424          8
         3          5       1432          8
         4          5       1440          8
         5          5       1448          8
         6          5       1456          8
         7          5       1464          8
         8          5       1472          8
         9          5       1480          8
        10          5       1488          8
        11          5       1496          8
        12          5       1504          8
        13          5       1512          8
        14          5       1520          8
        15          5       1528          8
        16          5       1536        128
        17          5       1664        128
        18          5       1792        128
        19          5       1920        128
        20          5       2048        128
        21          5       2176        128
        22          5       2304        128