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.
