Friday, August 9, 2013

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

2 comments :

  1. Hello, I'm only a developer trying to look for the last DML action on a table. Your query here provides the time, but is there a way to get what the action was that was performed on the table? Whether it was a INSERT/UPDATE or a DELETE ?

    ReplyDelete
  2. you will need to setup your database in Audit mode for that.

    ReplyDelete