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.

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.

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