Tag Archives: database

Tracking row changes in Oracle tables

It’s been a while since I posted anything work related but here’s one I discovered today.

Have you ever needed to find out when a particular row was changed? From Oracle 10g there’s a feature which records the SCN number of a row change within the table in a pseudo-column called ora_rowscn. So for example:

SQL> SELECT ora_rowscn FROM sometable WHERE id = '123';


Then you can convert the SCN number to a timestamp using the scn_to_timestamp function

SQL> SELECT scn_to_timestamp(45738231) AS timestamp FROM dual;


The only caveat with this feature is the tables have to be created with the ROWDEPENDENCIES option although it seems to be on as default in 11g.

Credits: http://oracle-tns.com/track-the-row-change-time/