1

Trying to run the following SQL which is basically an exact copy of an example I have seen on multiple sites, showing how to do this (I just change the tablename)

SELECT
SCN_TO_TIMESTAMP(ORA_ROWSCN) AS scn_time, ...
FROM
TABLE_ONE
WHERE
...;

But when I run it, it returns this error:

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
08181. 00000 -  "specified number is not a valid system change number"
*Cause:    supplied scn was beyond the bounds of a valid scn.
*Action:   use a valid scn.

What can I change to get this working? I am just looking to find the date/time a row was last updated.

Cheers in advance

K

2
  • According to documentation (docs.oracle.com/database/121/SQLRF/…), ORA_ROWSCN is not guaranteed to reflect the last modified time for the specific row. "The only guarantee is that the value of ORA_ROWSCN ... is greater than the commit SCN of the transaction that last modified that row." If you really need to track last modified timestamp, then you should have a specific auditing solution in place (triggers, fine-grained auditing, etc.) to handle the requirement. – pmdba Apr 8 '20 at 16:36
  • As far as your specific error goes, what value does ORA_ROWSCN actually return? – pmdba Apr 8 '20 at 16:38
0

SCN_TO_TIMESTAMP

The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.

Meaning the databases does not keep track of SCN-timestamp map forever and it is normal for SCN_TO_TIMESTAMP to fail for "too old" values. With the SCN returned from ORA_ROWSCN, you can still try to find the period of the change from views such as V$LOG_HISTORY (FIRST_CHANGE#, NEXT_CHANGE#, FIRST_TIME, NEXT_TIME).

When the time of the last update is required, the actual value is typically maintained and stored in an extra column in the table. ORA_ROWSCN is not good enough for that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.