ORA-08181: specified number is not a valid system change number

把scn号转成时间时遇到了报错ORA-08181: specified number is not a valid system change number

记录一下原因

SQL> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM emp; 
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

Cause:

If you provide a value into SCN_TO_TIMESTAMP which is not a System Change Number or if the SCN is tool old then you will get this error.

You can check the oldest available SCN number in database.

select min(SCN) min_scn from sys.smon_scn_time;

MIN_SCN

160715227536

SELECT SCN_TO_TIMESTAMP(160715227536) FROM dual;

SCN_TO_TIMESTAMP(160715227536)

2014-07-19 15:10:36,000000000

 If we check older SCN than minimum SCN then we will get the error.

SELECT SCN_TO_TIMESTAMP(160715227535) FROM dual;

ORA-08181: specified number is not a valid system change number

ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

参考

ORA-06512: at "SYS.SCN_TO_TIMESTAMP" - Mohammad Nazmul Huda

ORA-08181 When Select Scn_to_timestamp(Ora_rowscn) (文档 ID 361480.1)