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.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s