轉---DBMS_ROWID.ROWID_CREATE來解決壞塊

所有的ORACLE文件在提到HINT時，都會指出，HINT的優先順序最高。但是今天卻發現了意外情況。

今天開發人員報告測試庫出現一個錯誤：

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 ;

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.