CODEWITHSUNDEEP
oracle-databaseplsqloracle11gtoad
CompEng
CompEng

Reputation: 7376

Why truncate table does not change the Last DDL Date?

When I run the following code, I notice that Last DDL Date has not changed.

 execute immediate 'truncate table my_table';

What is the problem?

thanks in advance

Upvotes: 2

Views: 1186

Answers (2)

Popeye
Popeye

Reputation: 35920

The truncate table only truncates the table if it is not empty.

If the table is empty then it is not truncated when you execute the truncate table command.

See the demo here:

SQL> select OBJECT_NAME, LAST_DDL_TIME from user_objects where OBJECT_NAME = 'CONTR';

OBJECT_NAME  LAST_DDL_TIME
------------ --------------------
CONTR        13-nov-2019 11:45:53

SQL> truncate table CONTR; -- empty table

Table truncated.

SQL> select OBJECT_NAME, LAST_DDL_TIME from user_objects where OBJECT_NAME = 'CONTR';

OBJECT_NAME  LAST_DDL_TIME
------------ --------------------
CONTR        13-nov-2019 11:45:53

SQL> INSERT INTO CONTR VALUES (1,1,1,1); -- filling values in the table

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table CONTR;

Table truncated.

SQL> select OBJECT_NAME, LAST_DDL_TIME from user_objects where OBJECT_NAME = 'CONTR';

OBJECT_NAME  LAST_DDL_TIME
------------ --------------------
CONTR        13-nov-2019 11:47:46

SQL> -- time is changed now

Cheers!!

Upvotes: 4

CompEng
CompEng

Reputation: 7376

I found the answer.

The thing is :

Table is empty.

When a table is empty your truncate ddl does not change the Last DDL Date.

Upvotes: 1

Related Questions