Oracle 10g maintains a pseudocolumn the tracks the revision number of records. It’s called ORA_ROWSCN. (SCN stands for System Change Number.) There’s also a handy function, SCN_TO_TIMESTAMP, which will convert that revision number to a human-readable date and time.
But there are a few caveats:
- The revision numbers are tracked on a block level, meaning that even if you modify only one row, surrounding rows may also receive the new version number. You can turn on row-level tracking when you create the table, but there’s no way to do so after the fact.
- SCN_TO_TIMESTAMP will only be able to convert the SCN to a timestamp if the revision occurred within the last five days. Otherwise you get the lovely error: “ORA-08181: specified number is not a valid system change number”.
This article has a good description and two enlightening examples for those interested.