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';

ORA_ROWSCN
----------
45738231

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;

TIMESTAMP
------------------------------
05-JUL-11 10.44.42.000000000

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/