Julian Jewel’s Weblog » Blog Archive » ORA-06512: at “SYS.SCN_TO

ORA-06512: at “SYS.SCN_TO_TIMESTAMP”

March2

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.

posted under Server-Side Architecture

One Comment to

“ORA-06512: at “SYS.SCN_TO_TIMESTAMP””

On October 13th, 2009 at 4:45 pm Oracle 10g ORA_ROWSCN Pseudocolumn « Global Constant Says:
[...] 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”. Read more here. [...]