Truncate and data_object_id-优快云博客

本文通过实验展示了在Oracle数据库中使用TRUNCATE操作时，DATA_OBJECT_ID的变化规律。特别是针对带有索引的表进行TRUNCATE操作时，DATA_OBJECT_ID会增加2的情况进行了详细说明。

默认的，一个表当进行truncate 表时DATA_OBJECT_ID将增加1；
但是当此表有一个索引时，进行truncate 表时DATA_OBJECT_ID将增加2：

SQL>
SQL> create table x ( x number );

Table created.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779899 01-JUL-14 01-JUL-14

SQL> truncate table x;

Table truncated.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779899 01-JUL-14 01-JUL-14

SQL> insert into x values ( 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from x;

COUNT(1)
----------
1

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779899 01-JUL-14 01-JUL-14

SQL> truncate table x;

Table truncated.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779900 01-JUL-14 01-JUL-14

SQL> truncate table x;

Table truncated.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779900 01-JUL-14 01-JUL-14

SQL> insert into x values ( 1 );

1 row created.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779900 01-JUL-14 01-JUL-14

SQL> commit;

Commit complete.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779900 01-JUL-14 01-JUL-14

SQL> truncate table x;

Table truncated.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779901 01-JUL-14 01-JUL-14

SQL>
SQL>
SQL>
SQL> create index kk on x(x);

Index created.

SQL> select count(1) from x;

COUNT(1)
----------
0

SQL> insert into x values ( 1 );

1 row created.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779901 01-JUL-14 01-JUL-14

SQL> commit;

Commit complete.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779901 01-JUL-14 01-JUL-14

SQL> truncate table x;

Table truncated.

SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM CREATED
---------- -------------- ------------ ------------
779899 779904 01-JUL-14 01-JUL-14

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

转载于:http://blog.itpub.net/13750068/viewspace-1200843/