【原创】Truncate table对x$bh的影响

今天在做实验的时候发现：truncate table后x$bh不会再记录对此table块的访问，具体实验如下：

SQL>create table t3 ( a number);

Table created.

SQL>select object_id from dba_objects where object_name='T3';

OBJECT_ID
----------
51560

SQL>select obj,tch,dbarfil,dbablk from x$bh where bj=51560;

OBJ TCH DBARFIL DBABLK
---------- ---------- ---------- ----------
51560 2 1 60465

SQL>insert into t3 select level from dual connect by level<=1000;

1000 rows created.

SQL>select obj,tch,dbarfil,dbablk from x$bh where bj=51560;

OBJ TCH DBARFIL DBABLK
---------- ---------- ---------- ----------
51560 1 1 60467
51560 1 1 60466
51560 3 1 60465

SQL>truncate table t3;

Table truncated.

SQL>select obj,tch,dbarfil,dbablk from x$bh where bj=51560;

no rows selected

SQL>insert into t3 select level from dual connect by level<=1000;

1000 rows created.

SQL>commit;

Commit complete.

SQL>select obj,tch,dbarfil,dbablk from x$bh where bj=51560;

no rows selected

但是如果table中没有任何数据（只有一个块记录段头）时，x$bh就不会受truncate影响

SQL>create table t4 (a number);

Table created.

SQL>select object_id from dba_objects where object_name='T4';

OBJECT_ID
----------
51566

SQL>select obj,tch,dbarfil,dbablk from x$bh where bj=51566;

OBJ TCH DBARFIL DBABLK
---------- ---------- ---------- ----------
51566 1 1 60473

SQL>truncate table t4;

Table truncated.

SQL>select obj,tch,dbarfil,dbablk from x$bh where bj=51566;

OBJ TCH DBARFIL DBABLK
---------- ---------- ---------- ----------
51566 2 1 60473

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

转载于:http://blog.itpub.net/498744/viewspace-237821/