Skip to content
Home » Oracle » LAST_DDL_TIME, How and Why

LAST_DDL_TIME, How and Why

LAST_DDL_TIME

Generally speaking, LAST_DDL_TIME in ALL_OBJECTS or DBA_OBJECTS means that the last time of the object definition changed by Data Definition Language (DDL).

But sometimes, it's updated without any change on the definition. Let's see a case, we check LAST_DDL_TIME of a table.

First of all, we format the date time of current session.

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

Session altered.

Then perform the query.

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.

Next, we do a TRUNCATE TABLE.

SQL> truncate table hr.job_history;

Table truncated.

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, REVOKE and CREATE INDEX on the table are DDL statements, they are able to change LAST_DDL_TIME without touching definition.

Leave a Reply

Your email address will not be published. Required fields are marked *