February 2009 Archives

10gR2里执行计划显示的一些增强

By cui hua on February 26, 2009 3:47 PM | No Comments

确实方便了好多！如下所示：

SQL> conn scott/tiger@ipradev;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID  ch8faj4xqm1j3, child number 0

-------------------------------------

select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

------------------------------------------------------------------------------------------------

| Id  | Order | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 |     5 | SELECT STATEMENT             |         |       |       |     5 (100)|          |

|   1 |     4 |  NESTED LOOPS                |         |    14 |   350 |     5   (0)| 00:00:01 |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

|   2 |     1 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |

|   3 |     3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    12 |     1   (0)| 00:00:01 |

|*  4 |     2 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |

------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

-----------------------------------------------------------

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

 

已选择21行。

 

SQL> select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID  ch8faj4xqm1j3, child number 0

-------------------------------------

select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where

t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

---------------------------------------------------------

| Id  | Operation                    | Name    | E-Rows |

---------------------------------------------------------

|   1 |  NESTED LOOPS                |         |     14 |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

|   2 |   TABLE ACCESS FULL          | EMP     |     14 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |

---------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

Note

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

 

 

已选择27行。

 

SQL> select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.de

ptno=t2.deptno;

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID  4m81jub7yju91, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where

t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

--------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------

|   1 |  NESTED LOOPS                |         |      1 |     14 |     14 |00:00:00.01 |      24 |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

|   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |     14 |00:00:00.01 |      16 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     14 |      1 |     14 |00:00:00.01 |       2 |

--------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

 

已选择21行。

 

SQL> select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.de

ptno=t2.deptno;

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

---------- ---------- --------------

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID  4m81jub7yju91, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where

t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

----------------------------------------------------------------------------------------------------

| Id  | Order | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   1 |     4 |  NESTED LOOPS                |         |      1 |     14 |     14 |00:00:00.01 |      24 |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

|   2 |     1 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8

|   3 |     3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |     14 |00:00:00.01 |   

|*  4 |     2 |    INDEX UNIQUE SCAN         | PK_DEPT |     14 |      1 |     14 |00:00:00.01 |      

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

-----------------------------------------------------------

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

 

已选择21行。

 

xplan是对dbms_xplan的增强，从xplan里可以清晰的看到执行计划的顺序，它的源码在这里：

xplan.sql 

 

E-Rows 表示Estimated Rows

A-Rows 表示Actual Rows

A-Time 表示Actual Time

关于bind peeking

By cui hua on February 25, 2009 2:36 PM | No Comments

当你发现你执行某条sql时其执行计划时好时坏(oracle称这种情况为flip flop plans)，或者你在RAC的不同节点上执行同样的sql但发现执行计划并不一样的时候，你就要小心了，你可能是遇到了bind peeking。

 

这里，我们以一个简单的例子来重现flip flop plans：

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

SQL> create index idx_t1 on t1(object_id);

 

Index created

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     51660

 

SQL> select count(distinct(object_id)) from t1;

 

COUNT(DISTINCT(OBJECT_ID))

--------------------------

                     51659

 

这里为什么是51659而不是51660，是因为db link的object_id是null，如下所示：

SQL> select object_name,object_type from t1 where object_id is null;

 

OBJECT_NAME                                      OBJECT_TYPE

-------------------------------------------------------------------------------- -------------------

IPRADEV                                            DATABASE LINK

 

这里我们收集统计信息的时候让oracle自行来决定是否收集histograms及用多少个buckets：

SQL> exec dbms_stats.gather_table_stats(ownname => 'CAIPRA', tabname => 'T1', method_opt => 'for all indexed columns size auto', cascade => true);

 

PL/SQL procedure successfully completed

 

我们先来看不使用绑定变量情况下的执行计划：

SQL> set wrap off;

SQL> select count(*) from t1 where object_id between 999 and 1000;

 

  COUNT(*)

----------

         2

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  5gu397922cuqd, child number 0

-------------------------------------

select count(*) from t1 where object_id between 999 and 1000

 

Plan hash value: 4071255416

 

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE   |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID">=999 AND "OBJECT_ID"<=1000)

 

 

已选择19行。

 

SQL> select count(*) from t1 where object_id between 0 and 50000;

 

  COUNT(*)

----------

     47451

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  6tf6z6j1sjds7, child number 0

-------------------------------------

select count(*) from t1 where object_id between 0 and 50000

 

Plan hash value: 2174314726

 

--------------------------------------------------------------------------------

| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |        |       |       |    30 (100)|          |

|   1 |  SORT AGGREGATE       |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 41764 |   203K|    30  (10)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(("OBJECT_ID"<=50000 AND "OBJECT_ID">=0))

 

 

已选择19行。

这里我们可以看到一个的执行计划是index range scan，另外一个是index fast full scan，这和我们预期的一致！

 

好，现在我们来使用绑定变量来更改上述两句sql：

SQL> var x number;

SQL> var y number;

SQL> exec :x :=999;

 

PL/SQL 过程已成功完成。

 

SQL> exec :y :=1000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 0

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3858015043

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

Peeked Binds (identified by position):

--------------------------------------

 

   1 - :X (NUMBER): 999

   2 - :Y (NUMBER): 1000

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

 

SQL> exec :x := 0;

 

PL/SQL 过程已成功完成。

 

SQL> exec :y := 50000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     47451

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 0

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3858015043

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

Peeked Binds (identified by position):

--------------------------------------

 

   1 - :X (NUMBER): 999

   2 - :Y (NUMBER): 1000

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

看到了吗，当我们更改了绑定变量的值后，执行同样的sql，执行计划并没有改变，还是用的index range scan，而且oracle这里执行计划里绑定变量的peeking值还是999和1000，这就是所谓的bind peeking！

 

现在我们来让其执行计划变成index fast full scan：

SQL> grant select on t1 to scott;

 

授权成功。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     47451

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 0

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3581975568

 

--------------------------------------------------------------------------------

| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |        |       |       |    30 (100)|

|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   FILTER               |        |       |       |            |

|*  3 |    INDEX FAST FULL SCAN| IDX_T1 | 41764 |   203K|    30  (10)| 00:00:01

--------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

Peeked Binds (identified by position):

--------------------------------------

 

   1 - :X (NUMBER): 0

   2 - :Y (NUMBER): 50000

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

这里我们看到执行计划如我们所愿那样变成了index fast full scan，并且这里oracle重新对绑定变量x和y做了一次peeking！

 

现在我们再来换一种方式让bind peeking失效：

SQL> exec :x := 999;

 

PL/SQL 过程已成功完成。

 

SQL> exec :y := 1000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 0

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3581975568

 

--------------------------------------------------------------------------------

| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |        |       |       |    30 (100)|

|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   FILTER               |        |       |       |            |

|*  3 |    INDEX FAST FULL SCAN| IDX_T1 | 41764 |   203K|    30  (10)| 00:00:01

--------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

Peeked Binds (identified by position):

--------------------------------------

 

   1 - :X (NUMBER): 0

   2 - :Y (NUMBER): 50000

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

 

SQL> alter session set "_optim_peek_user_binds" = false;

 

会话已更改。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9dhu3xk2zu531, child number 1

-------------------------------------

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3858015043

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |   129 |   645 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter(:X<=:Y)

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择46行。

 

这里有两点需要注意：

1、即使你把_optim_peek_user_binds设置为false，因为Bug: 4567767的存在，bind peeking还是有可能会发生。

2、如果要重现我这篇文章里举的例子，请在sqlplus中重现，不要在PL/SQL Developer的command window中执行，为什么？你看了如下演示就知道了：

在sqlplus中执行如下语句一切正常：

SQL> select count(*) from uplbth;

 

  COUNT(*)

----------

         7

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  7v0k2uk56ag4b, child number 0

-------------------------------------

select count(*) from uplbth

 

Plan hash value: 2588870679

 

-----------------------------------------------------------------------------

| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                  |       |     1 (100)|          |

|   1 |  SORT AGGREGATE  |                  |     1 |            |          |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   2 |   INDEX FULL SCAN| PK_UPLBTH_UBTBTH |     7 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

 

已选择14行。

 

但如果你在PL/SQL Developer的command window中执行上述同样的sql，会碰到如下问题：

SQL> select count(*) from uplbth;

 

  COUNT(*)

----------

         7

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9m7787camwh4m, child number 1

begin :id := sys.dbms_transaction.local_transaction_id; end;

NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 1

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan

 

8 rows selected

也就是说当你在PL/SQL Developer的command window中执行sql的时候，从v$sql_plan抓到的sql实际上是begin :id := sys.dbms_transaction.local_transaction_id; end;

 

利用BBED修改checksum值的过程

By cui hua on February 23, 2009 4:13 PM | 2 Comments

我在"利用dd修改checksum值的过程"一文中详细描述了如何用dd修改一个指定block的checksum值的过程，现在我用BBED把这个改坏了的checksum值再改回来，如下的是详细的修改过程：

 

上述文章里我用dd改完后，从alert log里确实可以看到checksum值不对了：

Corrupt block relative dba: 0x2140000b (file 133, block 11)

Bad check value found during buffer read

Data in bad block -

 type: 6 format: 2 rdba: 0x2140000b

 last change scn: 0x0008.a9d0a5ea seq: 0x1 flg: 0x06

 consistency value in tail: 0xa5ea0601

 check value in block header: 0xb72a, computed block checksum: 0x7000

 spare1: 0x0, spare2: 0x0, spare3: 0x0

***

Reread of rdba: 0x2140000b (file 133, block 11) found same corrupted data

 

SQL_astca>select id from dras.t1;

 

        ID

----------

         1

         3

         4

         5

         6

         7

         8

 

7 rows selected.

 

SQL_astca>BEGIN

  2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  3  SCHEMA_NAME => 'DRAS',

  4  OBJECT_NAME => 'T1',

  5  OBJECT_TYPE => dbms_repair.table_object,

  6  FLAGS => dbms_repair.NOSKIP_FLAG);

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL_astca>select id from dras.t1;

ERROR:

ORA-01578: ORACLE data block corrupted (file # 133, block # 11)

ORA-01110: data file 133: '/dras20/astca/test01.dbf'

 

no rows selected

 

SQL_astca>DECLARE num_fix INT;

  2  BEGIN

  3  num_fix := 0;

  4  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

  5  SCHEMA_NAME => 'DRAS',

  6  OBJECT_NAME=> 'T1',

  7  OBJECT_TYPE => dbms_repair.table_object,

  8  REPAIR_TABLE_NAME => 'REPAIR_TABLE',

  9  FIX_COUNT=> num_fix);

 10  DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

 11  END;

 12  /

num fix: 0

 

PL/SQL procedure successfully completed.

 

SQL_astca>select id from dras.t1;

ERROR:

ORA-01578: ORACLE data block corrupted (file # 133, block # 11)

ORA-01110: data file 133: '/dras20/astca/test01.dbf'

 

no rows selected

 

这里可以看到用DBMS_REPAIR.FIX_CORRUPT_BLOCKS是不能修正一个block的checksum值错误的。

 

SQL_astca>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL_astca>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

好，现在我们来用BBED修改这个block的checksum值：

$ pwd

/cadrasu01/app/oracle/product/9.2.0/rdbms/lib

$ make -f ins_rdbms.mk $ORACLE_HOME/bin/bbed

make: 1254-002 Cannot find a rule to create target /cadrasu01/app/oracle/product/9.2.0/bin/bbed from dependencies.

Stop.

$ make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

 

Linking BBED utility (bbed)

        rm -f /cadrasu01/app/oracle/product/9.2.0/bin/bbed

        ld -b64 -o /cadrasu01/app/oracle/product/9.2.0/bin/bbed -L/cadrasu01/app/oracle/product/9.2.0/rdbms/lib/ -L/cadrasu01/app/oracle/product/9.2.0/lib/ /cadrasu01/app/oracle/product/9.2.0/lib/s0main.o /cadrasu01/app/oracle/product/9.2.0/rdbms/lib/ssbbded.o /cadrasu01/app/oracle/product/9.2.0/rdbms/lib/sbbdpt.o `cat /cadrasu01/app/oracle/product/9.2.0/lib/ldflags`    -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 /cadrasu01/app/oracle/product/9.2.0/rdbms/lib/defopt.o  -ldbtools9  `cat /cadrasu01/app/oracle/product/9.2.0/lib/ldflags`    -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 `cat /cadrasu01/app/oracle/product/9.2.0/lib/ldflags`    -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lclient9  -lvsn9 -lwtc9  -lcommon9 -lgeneric9 -lwtc9 -lmm -lnls9  -lcore9 -lnls9 -lcore9  -lnls9 -lxml9 -lcore9 -lunls9 -lnls9 `cat /cadrasu01/app/oracle/product/9.2.0/lib/ldflags`    -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 `cat /cadrasu01/app/oracle/product/9.2.0/lib/ldflags`    -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lclient9  -lvsn9 -lwtc9  -lcommon9 -lgeneric9 -lpls9  -ltrace9 -lnls9  -lcore9 -lnls9 -lcore9  -lnls9 -lxml9 -lcore9 -lunls9 -lnls9  -lclient9  -lvsn9 -lwtc9  -lcommon9 -lgeneric9 -lnls9  -lcore9 -lnls9 -lcore9  -lnls9 -lxml9 -lcore9 -lunls9 -lnls9  -lld -lm `cat /cadrasu01/app/oracle/product/9.2.0/lib/sysliblist`  -lm  `if [ "\`/usr/bin/uname -v\`" = "4" ]; \

        then echo "-bI:/cadrasu01/app/oracle/product/9.2.0/lib/pw-syscall.exp"; fi;` -lxsd9 /cadrasu01/app/oracle/product/9.2.0/lib/nautab.o /cadrasu01/app/oracle/product/9.2.0/lib/naeet.o /cadrasu01/app/oracle/product/9.2.0/lib/naect.o /cadrasu01/app/oracle/product/9.2.0/lib/naedhs.o

 

$ pwd

/cadrasu01/app/oracle/product/9.2.0/bin

 

$ cat filelist.txt

1       /dras11/oradata/astca/system01.dbf      524288000

125     /dras21/astca/system02.dbf      1048576000

133     /dras20/astca/test01.dbf        1048576

 

$ cat par.bbd

blocksize=8192

listfile=filelist.txt

mode=edit

 

 

bbed默认password是blockedit

$ bbed parfile=par.bbd

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Mon Feb 23 14:54:56 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set file 133

        FILE#           133

 

BBED> set block 11

        BLOCK#          11

 

BBED> set offset 16

        OFFSET          16

 

BBED> show

        FILE#           133

        BLOCK#          11

        OFFSET          16

        DBA             0x2140000b (557842443 133,11)

        FILENAME        /dras20/astca/test01.dbf

        BIFILE          bifile.bbd

        LISTFILE        filelist.txt

        BLOCKSIZE       8192

        MODE            Edit

        EDIT            Unrecoverable

        IBASE           Dec

        OBASE           Dec

        WIDTH           80

        COUNT           512

        LOGFILE         log.bbd

        SPOOL           No

 

BBED> modify /x 0xC7

 File: /dras20/astca/test01.dbf (133)

 Block: 11               Offsets:   16 to  527           Dba:0x2140000b

------------------------------------------------------------------------

 c72a0000 01000000 00015020 a9d0a5e9 00080000 00020200 00000000 00050022

 0001b6e8 12c0002a 46d40e00 20010000 a9d0a5ea 00000000 00000000 00000000

 00000000 00000000 00000000 00010001 ffff0014 0821080d 080d0000 00010821

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 133, Block 11:

current = 0xc72a, required = 0xc72a

 

BBED> exit

 

$ sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 23 16:06:58 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

 

SQL_astca>startup

ORACLE instance started.

 

Total System Global Area  824150304 bytes

Fixed Size                   743712 bytes

Variable Size             285212672 bytes

Database Buffers          536870912 bytes

Redo Buffers                1323008 bytes

Database mounted.

Database opened.

SQL_astca>select id from dras.t1;

 

        ID

----------

         1

         2

         3

         4

         5

         6

         7

         8

 

8 rows selected.

 

这里我们可以看到，checksum值已经被我们成功的改回来了！id为2的那条记录现在可以显示出来了！

关于我

By cui hua on February 22, 2009 10:05 PM | 6 Comments

Name:  Cui Hua

  QQ:  10083660

MSN:  allan_trey@hotmail.com

 Email:  allantreycn@yahoo.com.cn

Mobile:  139 1012 2046

 

我叫崔华，网名dbsnake，ACOUG成员，TechTarget China特邀专家顾问。

 

对Oracle的体系结构有深入了解。深入理解Oracle的内存结构、物理存储（各种块格式）、锁机制、优化机制等。

 

深入了解Oracle的备份恢复机制，熟悉Oracle的各种备份方法，能够处理各种情况下的数据恢复，包括没有数据备份时的恢复。

 

 

paste一下我的照片：

snake1.JPG  

利用dd修改checksum值的过程

By cui hua on February 18, 2009 2:55 PM | 4 Comments

本文参考了如下文章：

1、metalink上 268302.1 ORA-8103 Diagnostics and Solution

2、熊哥的记一次ORA-8103错误的处理

 

看到上述两篇文章，很有感触，禁不住手痒，我这里详细记录一下我用dd修改一个指定block的checksum值从而人为制造出坏块的过程，当然，最后我会用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS把上述坏块给标记出来，从而抢救出其他数据。

 

SQL> conn dras/astca@astcatest;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> create tablespace testtbs datafile '/dras20/astca/test01.dbf' size 1M;

 

Tablespace created

 

SQL> select tablespace_name,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='TESTTBS';

 

TABLESPACE_NAME                EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT

------------------------------ ----------------- ------------------------

TESTTBS                        LOCAL             MANUAL

 

SQL> create table t1 (id number, c1 char(2000), c2 char(2000), c3 char(2000)) tablespace testtbs;

 

Table created

 

SQL>

SQL> insert into t1 VALUES (1, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (2, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (3, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (4, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (5, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (6, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (7, 'A', 'A', 'A');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select segment_name,extent_id,block_id from dba_extents where segment_name='T1';

 

SEGMENT_NAME   EXTENT_ID   BLOCK_ID

-------------------------------------------------------------------------------- ---------- ----------

T1                       0          9

 

SQL> insert into t1 VALUES (8, 'A', 'A', 'A');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select segment_name,extent_id,block_id from dba_extents where segment_name='T1';

 

SEGMENT_NAME   EXTENT_ID   BLOCK_ID

-------------------------------------------------------------------------------- ---------- ----------

T1                     0          9

T1                     1         17

 

SQL> select id,rowid,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) from t1;

 

        ID ROWID              DBMS_ROWID.ROWID_RELATIVE_FNO(

---------- ------------------ --------------------------------------------------------------------------------

         1 AAAVAgACFAAAAAKAAA 133_10

         2 AAAVAgACFAAAAALAAA 133_11

         3 AAAVAgACFAAAAAMAAA 133_12

         4 AAAVAgACFAAAAANAAA 133_13

         5 AAAVAgACFAAAAAOAAA 133_14

         6 AAAVAgACFAAAAAPAAA 133_15

         7 AAAVAgACFAAAAAQAAA 133_16

         8 AAAVAgACFAAAAARAAA 133_17

 

8 rows selected

 

现在我要故意把ID为2的那条记录的checksum值给改掉。

方法就是用dd，如下是完整的改动过程：

$ sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 18 13:48:56 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

 

SQL_astca>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL_astca>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

$ dd if=/dras20/astca/test01.dbf of=/dras20/astca/test01_01.txt skip=11 bs=8192 count=1

1+0 records in.

1+0 records out.

$ dd if=/dras20/astca/test01.dbf of=/dras20/astca/test01_02.txt skip=12 bs=8192 count=117

117+0 records in.

117+0 records out.

$ ls -l

total 2463736

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassda0701_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drassdc0610_03.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassdc0701_02.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0610_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0705_01.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drasupd0706_02.dbf

-rw-r--r--   1 oracle   dba              17 Feb 18 13:48 login.sql

-rw-r-----   1 oracle   dba         1056768 Feb 18 13:56 test01.dbf

-rw-r--r--   1 oracle   dba            8192 Feb 18 13:59 test01_01.txt

-rw-r--r--   1 oracle   dba          958464 Feb 18 13:59 test01_02.txt

-rw-r-----   1 oracle   dba         1056768 Feb 18 13:54 test01backup.dbf

 

然后将test01_01.txt以binary方式ftp到本地，用ultraEdit打开，并将其第16个byte的值由C7改为B7，保存。然后将保存后的test01_01.txt再通过ftp以binary方式上传并覆盖原有同名文件。

 

覆盖完后再用dd把这个修改过checksum值的block给拷回去：

$ dd if=/dras20/astca/test01_01.txt of=/dras20/astca/test01.dbf seek=11 bs=8192

1+0 records in.

1+0 records out.

 

$ dd if=/dras20/astca/test01_02.txt of=/dras20/astca/test01.dbf seek=12 bs=8192

117+0 records in.

117+0 records out.

$ ls -l

total 2463720

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassda0701_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drassdc0610_03.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassdc0701_02.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0610_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0705_01.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drasupd0706_02.dbf

-rw-r--r--   1 oracle   dba              17 Feb 18 13:48 login.sql

-rw-r-----   1 oracle   dba         1056768 Feb 18 14:06 test01.dbf

-rw-r--r--   1 oracle   dba            8192 Feb 18 14:04 test01_01.txt

-rw-r--r--   1 oracle   dba          958464 Feb 18 13:59 test01_02.txt

-rw-r-----   1 oracle   dba         1056768 Feb 18 13:54 test01backup.dbf

$ sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 18 14:06:17 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

 

SQL_astca>startup

ORACLE instance started.

 

Total System Global Area  824150304 bytes

Fixed Size                   743712 bytes

Variable Size             285212672 bytes

Database Buffers          536870912 bytes

Redo Buffers                1323008 bytes

Database mounted.

Database opened.

 

这时候查询表t1里的数据，果然，oracle这里报错有坏块，如下所示：

SQL> conn dras/astca@astcatest;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> select id from t1;

 

select id from t1

 

ORA-01578: ORACLE data block corrupted (file # 133, block # 11)

ORA-01110: data file 133: '/dras20/astca/test01.dbf'

 

现在我们来skip上述坏快，把其他的数据给救出来：

SQL_astca>BEGIN

  2  DBMS_REPAIR.ADMIN_TABLES (

  3  TABLE_NAME => 'REPAIR_TABLE',

  4  TABLE_TYPE => dbms_repair.repair_table,

  5  ACTION => dbms_repair.create_action,

  6  TABLESPACE => 'TESTTBS');

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL_astca>set serveroutput on

SQL_astca>DECLARE num_corrupt INT;

  2  BEGIN

  3  num_corrupt := 0;

  4  DBMS_REPAIR.CHECK_OBJECT (

  5  SCHEMA_NAME => 'DRAS',

  6  OBJECT_NAME => 'T1',

  7  REPAIR_TABLE_NAME => 'REPAIR_TABLE',

  8  corrupt_count => num_corrupt);

  9  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

 10  END;

 11  /

number corrupt: 1

 

PL/SQL procedure successfully completed.

 

SQL_astca>select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION

  2  from REPAIR_TABLE;

 

  BLOCK_ID CORRUPT_TYPE

---------- ------------

CORRUPT_DESCRIPTION

--------------------------------------------------------------------------------

        11         6148

 

 

SQL_astca>BEGIN

  2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  3  SCHEMA_NAME => 'DRAS',

  4  OBJECT_NAME => 'T1',

  5  OBJECT_TYPE => dbms_repair.table_object,

  6  FLAGS => dbms_repair.SKIP_FLAG);

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

做完后我们再次查询表t1里的数据，可以看到，除了id为2的那条记录外，其他的数据都已经被救出来了：

SQL> select id from t1;

 

        ID

----------

         1

         3

         4

         5

         6

         7

         8

 

7 rows selected

 

2008年我写的一些文章

By cui hua on February 11, 2009 4:15 PM | No Comments

我原先的Blog是在oraclefocus.zhan.cn.yahoo.com

但后来yahoo通知我说这个Blog将被无条件关闭，无奈之下迁移到这里。

免费的午餐长久不了！

我这里将我2008年写过的一些文章paste到这里，算是对我原先那个Blog的一种交代。

ASM的disk header的备份与恢复.doc

IPRADEV中查看执行计划功能的增强.doc

oracle中select数据时默认的显示顺序(续).doc

记一次调优的过程.doc

解决.net应用程序一切换中文输入法就死的例子.doc

解决透明网关故障一例.doc

解决应用一个插入数据死机的案例.doc

弄清楚out join的原则.doc

如何清理oracle安装目录里的一些日志.doc

数据表查找修改的问题.doc

修正netca不能配置服务的问题.doc

用streams实现不同数据库下不同schema间DDL同步.doc

用高级复制实现主文件同步方案(第六版).doc

在3650上装10gR2 RAC.doc

在Red Hat Linux AS4上安装Oracle 10gR2 RAC.doc

10gR2中查询IOPS和BPS的sql.doc 

权限与角色.doc

在windows平台搭建physical standby的过程.doc

My wish for 2009

By cui hua on February 9, 2009 3:56 PM | 1 Comment

Here is my 2009 wish list for myself.

 

I hope that I and my family are in good health.

I hope I can get driver's license.

I hope I can get better in oracle and db2.

I hope that I can lead my team to do a good job in ARMS. This is very important for me! Air China paid 60 million RMB for the use of ARMS in 2007, China Eastern also paid 50 million RMB for the use of ARMS in 2007, ARMS has brought revenue of 150,000,000 RMB for my company in 2007. We hope to complete the ARMS development plan in 2009 in any case!

 

Happy New Year to everyone and may all your wishes come true!
« January 2009 | Main Index | Archives | March 2009 »

Search

Recent Comments

  • the diet solution program reviews: By far, one of the best post l have come read more
  • Lose Ten Pounds in Three Days: By a long shot, one of the best post l read more
  • exercises to help lose 15 pounds: Without doubt, one of the best article l have come read more
  • Lose 20 Pounds a Week: I certainly enjoy your post, but having problem subscribing to read more
  • Aiko Potsander: Please continue to keep the good work! Cheers. read more
  • 毕业论文: 学习了。 read more
  • cui hua: 不用改数据，你改row directory里的指针就可以了——这就是我文中提到的update internal。 read more
  • yangjiawei: 领导，不好意思，再请问一下 我现在遇到一个问题，我现在已经将ind$里两个索引的状态改好了，数据库也拉起来了~！ 但是在修改obj$里name里为DEPENDENCY$这一行数据的data_object_id时遇到了困难，因为他原先的长度为2个字节，现在由于我move了一下，他的长度变成了4个字节，结果如下： 原先： col 1[2] @906: 0xc1 0x5d ==>92 read more
  • cui hua: 可以从上述sql中推断出等待的顺序（如何推断我已经在文中提到了），是不是太直观，但是综合上述症状来看，只有我文中提到的这种可能。 read more
  • yangjiawei: 我最近也正好再研究这些比较Internal的东西，所以想麻烦您能对这个问题写一个比较详细的解释及解决方案，我对这些也是比较感兴趣的，可以吗？ read more

Recent Entries

Tag Cloud

About this Archive

This page is an archive of entries from February 2009 listed from newest to oldest.

January 2009 is the previous archive.

March 2009 is the next archive.

Find recent content on the main index or look in the archives to find all content.