How to determine the last modified date of tables in Oracle ?

For each row in a table, ORA_ROWSCN returns the SCN of the most recent change in the row.?This pseudo-column can be used to approximate when a row was last updated.?ORA_ROWSCN information in table rows can be queried as follows.

For row-based change tracking, the relevant table must be created by specifying ROWDEPENDENCIES.?Unless specified, tables are created as NOROWDEPENDENCIES.?Each row in tables created as ROWDEPENDENCIES will contain an extra 6 bytes of data.

SQL> create table a(id number,ad varchar(50)) ROWDEPENDENCIES;        

ORA_ROWSCN information of rows like below can be queried.

SQL> select to_char(ORA_ROWSCN),ID  from aduruoz.a;

TO_CHAR(ORA_ROWSCN)			 ID
-------------------------- -------
1301894904749				  1
1301894900367				  2
1301894900627				  3
1301894903961				  4	        

The largest ORA_ROWSCN value will also give the date of the most recent change to a table.

SQL> select max(to_char(ORA_ROWSCN)) from aduruoz.a;

MAX(TO_CHAR(ORA_ROWSCN))
------------------------------------------------------------------------------------------------------------------------
1301894904749        

You can convert the ORA_ROWSCN value to date with the following command.

SQL> select scn_to_timestamp(1301894904749) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
28-MAY-19 02.36.32.000000000 PM