truncate操作導致DATA_OBJECT_ID改變

Oracle中一般情況下表的OBJECT_ID與DATA_OBJECT_ID是一致的。

但在truncate後表達DATA_OBJECT_ID會發生改變。利用這一特性可以判斷表是否發生過truncate操作。

註：(DATABASE LINK,FUNCTION,PROCEDURE,SEQUENCE,VIEW)沒有DATA_OBJECT_ID。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report

SQL> SELECT t.table_name, t.last_analyzed, t.num_rows
  2    FROM USER_TABLES t
  3   WHERE t.table_name = 'T2';

TABLE_NAME LAST_ANALYZED       NUM_ROWS
---------- --------------------------          ----------
T2         2011-04-04 16:56:17                    3

--T2的 OBJECT_ID  與DATA_OBJECT_ID相同

SQL> SELECT object_name, object_type, object_id, data_object_id
  2    FROM USER_OBJECTS
  3   WHERE object_name = 'T2';

OBJECT_NAM OBJECT_TYPE          OBJECT_ID  DATA_OBJECT_ID
---------- -------------------                ----------         -----------------------
T2           TABLE                              75567                             75567

SQL> truncate table T2;
Table truncated

SQL>  analyze table t2 compute statistics;
Table analyzed

-- 對錶T2進行truncate後OBJECT_ID未發生改變，而DATA_OBJECT_ID由 75567 變為 76592

SQL> SELECT object_name, object_type, object_id, data_object_id
  2    FROM USER_OBJECTS
  3   WHERE object_name = 'T2';
OBJECT_NAM OBJECT_TYPE          OBJECT_ID       DATA_OBJECT_ID
----------         -------------------        ----------           --------------
T2                  TABLE                         75567             76592

SQL>