3

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?

2
  • What do you mean? – Colin 't Hart Sep 15 '15 at 12:04
  • 1
    I need a way to find a name of DML opration itself. It means something like string constant INSERT if the last op was insert into T.... – egor7 Sep 15 '15 at 12:11
4

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;

enter image description here

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

1
  • This is correct only for DMLs since the last time statistics had been collected. SELECT max(ora_rowscn) should be the proper method, but it is unbelievably slow if you have a lot of legacy tables and running 11g. – access_granted Mar 30 '17 at 20:16
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.