Pages

Friday, June 5, 2015

LAST DDL change time on a table in ORACLE

A general question among most of developers is  " HOW can I know when LAST DDL was done??" The best option will be to go and look at the table details. 
While you can also query the same details from user_objects/dba_objects.

Lets create a table test1

sql > create table test1 (id number);

table TEST1 created.


sql >  select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1';

OBJECT_NAME            OBJECT_TYPE         CREATED           LAST_DDL        
-------------------------------------------------    ---------------------     --------------
TEST1                              TABLE                     05-06-15 15:54:37        05-06-15 15:54:37 


Now lets try adding a column to table.

sql > alter table test1 add (id1 number);

table TEST1 altered.

sql > 
select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects where object_name='TEST1';

OBJECT_NAME      OBJECT_TYPE         CREATED                  LAST_DDL        
---------------------   ----------------------  ---------------------        -------------------
TEST1                       TABLE                         05-06-15 15:54:37          05-06-15 15:55:14 

We can see that Oracle now keeps track of latest DDL changes. It can give you an accurate time_stamp with above query.

1 comment :

  1. No you can't. At least not with out enabling the DB auditing or some kind of triggers setup to capture those dmls.
    But you can certainly get time, when it was done !!

    look at my below post:
    http://arvindasdba.blogspot.com/2013/07/finding-out-last-dml-activity-on-table.html

    Thanks
    Arvind

    ReplyDelete