How to get a last DML operation in Oracle 10g-11g

Question

I can get a number of last INSERT's/UPDATE's/DELETE's from SQL%ROWCOUNT var. Also there exists a way to get a last DML time of each row.

But is there a way to get a last DML operation performed on a table - INSERT/UPDATE/DELETE?

Answer 1

With this query you will see your last DML event by loonking on last change , most recent date is the last operation

  SELECT TABLE_OWNER, 
   TABLE_NAME, 
   INSERTS,
   UPDATES,
   DELETES,
   TIMESTAMP AS `LAST_CHANGE`
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY') 
ORDER BY LAST_CHANGE DESC;

But the only way to use it its separating the DML events in differents querys or adding three differents last_change per dml event. In any case you tell me your doubts

Answer 2

You can turn on user session auditing for that schema that is running the command. Then look at the SYS.$AUD table to see what that operation was with the timestamp. You can also turn on application context, which allows you using the package DBMS_APPLICATION_INFO to track the specific DML in tables like gv$sql using MODULE and ACTION columns. I'm sure there are other was as well.