ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转--DBMS_ROWID.ROWID_CREATE来解决坏块

转--DBMS_ROWID.ROWID_CREATE来解决坏块

原创 Linux操作系统 作者:leng138322 时间:2011-03-31 15:42:15 0 删除 编辑

DBMS_ROWID.ROWID_CREATE来解决坏块

2008-12-15 11:46

所有的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
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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15881763/viewspace-691394/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-07-08

  • 博文量
    35
  • 访问量
    86581