How to find when a table was last modified

For each row in a table, ORA_ROWSCN returns the SCN information of the last change made to that row. This pseudo-column can be used to approximately determine when a row was last updated. The ORA_ROWSCN information for table rows can be queried as follows.

To track changes on a row-by-row basis, the relevant table must be created with ROWDEPENDENCIES specified. If not specified, tables will be created with NOROWDEPENDENCIES. Tables created with ROWDEPENDENCIES will have an additional 6 bytes of data per row.

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

You can query the ORA_ROWSCN of rows  with the following sql:

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

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

The maximum ORA_ROWSCN value will also give the date of the last change made 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 a date using the following sql.

select scn_to_timestamp(1301894904749) as timestamp from dual;

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