Finding Last DDL and DML Activity on a Table

 

 Finding out Last DDL and DML Activity on a Table

Here is a small piece of SQL Commands to get the same info:

create table t (t1 number,t2 varchar2(20),t3 date);

Table created.

Here is how you could find the same

OracleDba.In >select
  2  (select last_ddl_time from dba_objects where object_name='T' and owner='SYS') "DDL Time",
  3   decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
  4  from
  5  (select nvl(max(ora_rowscn),0) maxscn from t);

DDL Time            DML Time
------------------- -------------------------------
2012-01-25 15:58:35 N/A

Now add some data to the table

sql >insert into t values(1,'A',sysdate);

1 row created.

sql  >select
          (select last_ddl_time from dba_objects where object_name='T' and owner='SYS') "DDL Time",
           decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
           from
          (select nvl(max(ora_rowscn),0) maxscn from t);

DDL Time            DML Time
------------------- -------------------------------
2013-01-25 15:58:35 25-JAN-13 04.05.14.000000000 PM

sql  >commit;

Commit complete.

OraDba.In >update t set t1=2;

1 row updated.


sql  >select
          (select last_ddl_time from dba_objects where object_name='T' and owner='SYS') "DDL Time",
           decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
           from
          (select nvl(max(ora_rowscn),0) maxscn from t);


DDL Time            DML Time
------------------- -------------------------------
2013-01-25 15:58:35 25-JAN-13 04.05.20.000000000 PM

OracleDba.In >alter table t move;

Table altered.


sql  >select
          (select last_ddl_time from dba_objects where object_name='T' and owner='SYS') "DDL Time",
           decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
           from
          (select nvl(max(ora_rowscn),0) maxscn from t);



DDL Time            DML Time
------------------- -------------------------------
2013-01-25 16:05:40 25-JAN-13 04.05.38.000000000 PM


Note:  You can also get all this information by enabling auditing in your database.

Check this links for Auditing : 

http://arvindasdba.blogspot.com/2014/11/oracle-audting-expained.html

http://arvindasdba.blogspot.com/2014/03/auditing-ddl-changes-in-oracle-database.html