March 2

We have a cache synchronizer which uses the SCN_TO_TIMESTAMP(ORW_ROWSCN) in one of our queries to check for the recently modified records and updates the cache. The problem with the SCN_TO_TIMESTAMP(ORA_ROWSCN) is that the SCN’s are only valid for 5 days.

So a query like SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) from SOME_TEST_TABLE; will return correctly if the recent ORA_ROWSCN was created in the last 5 days. Or you would get the following error.

java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP

Luckily we had a MODIFIED_DATE column that our application updates whenever a record is modified. So we ended up using that.