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

I have a table which is throwing error for some of the rows when try to convert ORA_ROWSCN for corresponding row to SCN_TO_TIMESTAMP as below:-

Select SCN_TO_TIMESTAMP(429804070) from dual; --14-NOV-22 07.52.22.000000000 AM
Select SCN_TO_TIMESTAMP(432572474) from dual; --16-NOV-22 02.00.59.000000000 AM
Select SCN_TO_TIMESTAMP(423859441) from dual; --ORA-08181: specified number is not a valid system change number
Select SCN_TO_TIMESTAMP(423859575) from dual; --ORA-08181: specified number is not a valid system change number

Kindly someone explain and provide the solution.

CodePudding user response：

That's because you're out of range.

SQL> show user
USER is "SYS"

Let's check the range first:

SQL> select min(scn), max(scn) from smon_scn_time;

  MIN(SCN)   MAX(SCN)
---------- ----------
  14831895   16817322

Apply scn_to_timestamp to MIN and MAX values - both of them are valid:

SQL> select SCN_TO_TIMESTAMP(14831895) min_scn,
  2         SCN_TO_TIMESTAMP(16817322) max_scn
  3  from dual;

MIN_SCN                          MAX_SCN
-------------------------------- --------------------------------
08-OCT-22 10.23.31.000000000 PM  19-NOV-22 10.11.36.000000000 PM

What if you try values that are out of that range (i.e. lower than MIN and higher than MAX)? None of them work, and that's your case:

SQL> select SCN_TO_TIMESTAMP(10831895) lower_than_min_scn from dual;
select SCN_TO_TIMESTAMP(10831895) lower_than_min_scn from dual
       *
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 SCN_TO_TIMESTAMP(19817322) higher_than_max_scn from dual;
Select SCN_TO_TIMESTAMP(19817322) higher_than_max_scn from dual
       *
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>