Skip to content

LAST_DDL_TIME was Changed, Why?

  • by

What is LAST_DDL_TIME?

Generally speaking, LAST_DDL_TIME means the last change date time of the object definition, all by Data Definition Language (DDL). But sometimes, it's been updated with no change on the definition.

Let's see a case, we check LAST_DDL_TIME of a table.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select last_ddl_time from all_objects where owner = 'HR' and object_type = 'TABLE' and object_name = 'JOB_HISTORY';

LAST_DDL_TIME
-------------------
2020-07-20 23:33:08

We checked the current LAST_DDL_TIME of the table by querying ALL_OBJECTS.

We do a TRUNCATE TABLE.

SQL> truncate table hr.job_history;

Table truncated.

We check LAST_DDL_TIME again.

SQL> select last_ddl_time from all_objects where owner = 'HR' and object_type = 'TABLE' and object_name = 'JOB_HISTORY';

LAST_DDL_TIME
-------------------
2022-01-27 21:46:52

As we can see, the table definition is not changed, but LAST_DDL_TIME is moved. Essentially, LAST_DDL_TIME keeps the last successful DDL time. That is to say, as long as the statement is a DDL, it's able to update LAST_DDL_TIME.

Yes, we know TRUNCATE TABLE removes all data instantly without changing table definition, which makes it look like a DML, but it's a DDL (Data Definition Language) statements actually.

Please note that, not only TRUNCATE, but also GRANT and REVOKE are DDL statements, they can change LAST_DDL_TIME without touching definition.

Leave a Reply

Your email address will not be published.