Guidance:
During the experiment today, we found that x $ BH after truncate table won't record the access to this table block. The specific experiment is as follows:
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 OBJ = 51560;
OBJ tch dbarfil dbablk
----------------------------------------
51560 2 1 60465
SQL> insert into T3 select level from dual connect by level 1000 rows created.
SQL> select OBJ, tch, dbarfil, dbablk from x $ BH where OBJ = 51560;
OBJ tch dbarfil dbablk
----------------------------------------
51560 1 60467
51560 1 60466
51560 3 1 60465
SQL> truncate table T3;
Table truncated.
SQL> select OBJ, tch, dbarfil, dbablk from x $ BH where OBJ = 51560;
No rows selected
SQL> insert into T3 select level from dual connect by level 1000 rows created.
SQL> commit;
Commit complete.
SQL> select OBJ, tch, dbarfil, dbablk from x $ BH where OBJ = 51560;
No rows selected
However, if the table does not contain any data (only one block record segment header), x $ BH will not be affected by 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 OBJ = 51566;
OBJ tch dbarfil dbablk
----------------------------------------
51566 1 60473
SQL> truncate table T4;
Table truncated.
SQL> select OBJ, tch, dbarfil, dbablk from x $ BH where OBJ = 51566;
OBJ tch dbarfil dbablk
----------------------------------------
51566 2 1 60473
This article is transferred from
Http://woodnan.itpub.net/post/37055/457768
