Ora-08181 When Select Scn_to_timestamp(Ora_rowscn)

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1
Information in this document applies to any platform.

Symptoms

You are attempting to use the SCN_TO_TIMESTAMP(ORA_ROWSCN) functions to return the row SCN value and are getting ora-8181 errors returned as demonstrated below:

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TABLENAME
GT1

6 rows selected.

SQL> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM dept;
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM dept
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


SQL> select ora_rowscn from dept;

ORA_ROWSCN
----------
529426
529426
529426
529426

Cause

This is expected behavior as the SCN must be no older than 5 days as part of the current flashback database
features.

Currently, the flashback query feature keeps track of times up to a
maximum of 5 days. This period reflects server uptime, not wall-clock
time. You must record the SCN yourself at the time of interest, such as
before doing a DELETE.