Wednesday, 31 January 2018

How to find a time for the latest DML on a table

Lately I've got such question from a co-worker. I looked around a bit and here are the findings:
  • ORA_ROWSCN pseudocolumn - seems to be the most versatile solution here - the maximal value of ORA_ROWSCN would definitely point the time of the last modification for any table.
  • VERSIONS_ENDSCN https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01009
  • dba_tab_modifications - this is the table, which keeps number of DML operations since the last statistics collection. Thus it may be used to find the approx. time of a last modification assuming it is done after the latest stats collection, but it has of course a number of limitations

No comments: