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.