Oracle中truncate表不更新last_ddl_time列
问题描述
最近发现数据库中定时job的某张表，每天都有truncate动作，由于调整了job的interval时间，想查看last_ddl_time辅助验证job的运行情况，结果发现last_ddl_time是几天前的时间。
单单从这点上看我还以为这个job没运行过，但是从last_date字段又可以看出最近的时间是执行过的，怎么回事呢？
测试分析
由于表示临时用的表，而且是一张空表，征得相关人员许可后，测试环境中（存在同样的问题）对表手工做truncate，再次查看last_ddl_time，果然没变化。
开始mos找资料，有这样一篇文章“LAST_DDL_TIME Not Changing (Doc ID 1448241.1)”，里边这样描述：
10g and subsequent do not invalidate dependent objects unnecessarily. Therefore, since the PACKAGE BODY is not changing, the LAST_DDL_TIME does not change.
If you would change something in the package body (a line of code, not simply a comment), you will find that LAST_DDL_TIME changes.
LAST_DDL_TIME changes for CREATE OR REPLACE ...
1. When the source of the procedure changes.
... OR ...
2. The settings of following parameters change from original creation time:
plsql_optimize_level, plsql_code_type, plsql_debug, nls_length_semantics, plsql_warnings, plsql_ccflags, plsql_compiler_flags.So, this is not a bug, but expected behavior. Last_ddl_time is only updated if the object actually changes.
虽然DOC上描述的场景和我遇到的情况不一样，不过里边有句话提醒了我。
If you would change something in the package body (a line of code, not simply a comment), you will find that LAST_DDL_TIME changes.
（如果您要更改程序包主体中的某些内容（一行代码，而不仅仅是注释），则会发现LAST_DDL_TIME发生了更改。）
So, this is not a bug, but expected behavior. Last_ddl_time is only updated if the object actually changes.
（因此，这不是错误，而是预期的行为。仅当对象实际更改时，Last_ddl_time才会更新。）
所以有可能是原表就是空表，你做truncate操作的时候，实际上就不会更新last_ddl_time这个列。
测试模拟
[oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 28 08:34:31 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SYS@zkm> startup;
ORACLE instance started. Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 465571384 bytes
Database Buffers 314572800 bytes
Redo Buffers 2596864 bytes
Database mounted.
Database opened. SYS@zkm> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SYS@zkm> select last_ddl_time from dba_objects where object_name='TEST'; LAST_DDL_TIME
-------------------
2020-04-26 10:37:05 SYS@zkm> !date
Tue Apr 28 08:35:27 CST 2020 SYS@zkm> desc zkm.test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER SYS@zkm> delete from zkm.test; 13107200 rows deleted. SYS@zkm> commit; Commit complete. SYS@zkm> truncate table zkm.test; Table truncated. SYS@zkm> select last_ddl_time from dba_objects where object_name='TEST'; LAST_DDL_TIME
-------------------
2020-04-28 08:43:24
从这里看，我将表的数据全部delete并且commit后，truncate表竟然last_ddl_time更新了。
随后我想到truncate表实际上是换了个段，data_object_id会变，那再次truncate的话应该不会再更新last_ddl_time了。
SYS@zkm> select object_id,data_object_id from dba_objects where owner='ZKM' and object_name='TEST'; OBJECT_ID DATA_OBJECT_ID
---------- --------------
18815 18832 SYS@zkm> truncate table zkm.test; Table truncated. SYS@zkm> select last_ddl_time from dba_objects where object_name='TEST'; LAST_DDL_TIME
-------------------
2020-04-28 08:43:24 SYS@zkm> select object_id,data_object_id from dba_objects where owner='ZKM' and object_name='TEST'; OBJECT_ID DATA_OBJECT_ID
---------- --------------
18815 18832
果然是这样，而且data_object_id也没变了。