今天测试人员报告测试库出现一个错误：

ORA-01578: ORACLE data block corrupted (file # 39, block # 24961)

ORA-01110: data file 39: '/oracle/oradata/test/ndmain09.dbf'

由于是测试库，因此没有做热备。测试库中的数据本身并不很重要，丢失一个BLOCK的数据是可以接受的。所以打算直接丢弃坏块中的数据。

由于没有必要尝试修复数据，没有必要使用DBMS_REPAIR包，利用设置EVENT导出的方式又相对比较麻烦，打算采用最简单的利用ROWID绕过坏块的方式来重建表。

首先，找到有问题的表：

SQL> SELECT SEGMENT_TYPE, OWNER, SEGMENT_NAME FROM DBA_EXTENTS
2 WHERE FILE_ID = 39
3 AND 24961 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1
4 ;

SEGMENT_TYPE OWNER SEGMENT_NAME
------------------ ------------------------------ ------------------------------
TABLE DATA CAT_ZONE_PRODUCT

下面构造坏块的ROWID：

SQL> CONN DATA
Enter password:
Connected.
SQL> SELECT DATA_OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME = 'CAT_ZONE_PRODUCT';

DATA_OBJECT_ID
--------------
54649

SQL> SELECT DBMS_ROWID.ROWID_CREATE(1, 54649, 39, 24961, 0) FROM DUAL;

DBMS_ROWID.ROWID_C
------------------
AAANV5AAnAAAGGBAAA

我们已经找到坏块的ROWID了，下面只需要将除了这个BLOCK以外的数据读出来就可以了：

SQL> CREATE TABLE CAT_ZONE_PRODUCT_BAK AS
2 SELECT /*+ ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 OR ROWID >= CHARTOROWID('AAANV5AAnAAAGGCAAA')
5 ;
CREATE TABLE CAT_ZONE_PRODUCT_BAK AS
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 39, block # 24961)
ORA-01110: data file 39: '/oracle/oradata/test/ndmain09.dbf'

奇怪，明明已经通过提示告诉Oracle跳过坏块了，怎么还报这个错误呢？看看执行计划吧：

SQL> EXPLAIN PLAN FOR
2 SELECT /*+ ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 OR ROWID >= CHARTOROWID('AAANV5AAnAAAGGCAAA')
5 ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164K| 1175M| 1985 |
| 1 | TABLE ACCESS FULL | CAT_ZONE_PRODUCT | 164K| 1175M| 1985 |
--------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

Session altered.

SQL> EXPLAIN PLAN FOR
2 SELECT /*+ ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 OR ROWID >= CHARTOROWID('AAANV5AAnAAAGGCAAA')
5 ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164K| 1175M| 1985 |
| 1 | TABLE ACCESS FULL | CAT_ZONE_PRODUCT | 164K| 1175M| 1985 |
--------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;

Session altered.

看来HINT没有起左右，可是从语法上看是没有任何问题的。难道是or的问题：

SQL> EXPLAIN PLAN FOR
2 SELECT /*+ ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84208 | 602M| 1985 |
| 1 | TABLE ACCESS BY ROWID RANGE| CAT_ZONE_PRODUCT | 84208 | 602M| 1985 |
-------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

这回结果对了，看来果然是or出的问题，不过既然HINT的优先级最高，为什么加上一个OR的条件，就导致Oracle的执行计划改变了呢，看来不是HINT优先级最高是有疑问的，就是这里出现了bug。

问题找到了，剩下的就简单了，将查询语句简单变形，再次执行：

SQL> CREATE TABLE CAT_ZONE_PRODUCT_BAK AS
2 SELECT /*+ ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 UNION ALL
5 SELECT /*+ ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
6 WHERE ROWID >= CHARTOROWID('AAANV5AAnAAAGGCAAA')
7 ;

Table created.

SQL> TRUNCATE TABLE CAT_ZONE_PRODUCT DROP STORAGE;

Table truncated.

SQL> INSERT INTO CAT_ZONE_PRODUCT SELECT * FROM CAT_ZONE_PRODUCT_BAK;

157186 rows created.

SQL> COMMIT;

Commit complete.