Latest DML time on a table

In this post we will see how to find the latest DML time performed on the table. For this we are going to use ORA_ROWSCN Pseudocolumn. ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated.  It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

We can use below query to find out the latest DML time performed on the table.


select to_char(max(ora_rowscn)) LATEST_SCN,scn_to_timestamp(max(ora_rowscn)) SCN_TIMESTAMP from <<TABLE_NAME>>;

Let’s see below example.


22:10:05 SQL>;create table TABLE_DML_TEST as select * from dba_objects;

Table created.

22:10:30 SQL>;

22:12:32 SQL>;select to_char(max(ora_rowscn)) LATEST_SCN,scn_to_timestamp(max(ora_rowscn)) SCN_TIMESTAMP from TABLE_DML_TEST;

LATEST_SCN SCN_TIMESTAMP
------------------------------ ----------------------------------------
426138027889 06-APR-16 10.10.28.000000000 PM

1 row selected.

22:12:43 SQL>;

Now we will update one of the row of the table and we will again execute the above query.


22:13:27 SQL>;update TABLE_DML_TEST set object_id=111111 where object_id=65081;

1 row updated.

22:14:00 SQL;

22:14:10 SQL>commit;

Commit complete.

SQL>select to_char(max(ora_rowscn)) LATEST_SCN,scn_to_timestamp(max(ora_rowscn)) SCN_TIMESTAMP from TABLE_DML_TEST;

LATEST_SCN SCN_TIMESTAMP
------------------------------ ----------------------------------------
426138028004 06-APR-16 10.14.09.000000000 PM

1 row selected.

Query output shows the latest DML time on a table.

References:

Latest DML time on a table

Leave a Reply