truncate操作导致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>