1

Hi I created Oracle flashback Archive with retention of 1 month and enabled this archive on few of the tables. But when i execute a versions query like below, i get Error "ORA-08181: specified number is not a valid system change number. ORA-06512: at "SYS.TIMESTAMP_TO_SCN". " And i am not getting this consistently, Sometimes i can query way back 10 days and for some tables i cannot query past 2 days.

select versions_starttime from tbl1 versions between timestamp minvalue and maxvalue or select versions_starttime from tbl1 versions between timestamp sysdate-2 and sysdate

We do have AUTO undo management and undo retention is 24 hours and retention guarantee is set. As per many forums, its mentioned we get this Error when we try to look far back and as per the below link, it should be max( auto-tuned undo retention period, retention times of all flashback archives in the database).

https://docs.oracle.com/database/121/SQLRF/functions175.htm#SQLRF06325

Can someone help why we get this Error irrespective of FDA retention being one month?

mediocre_10
  • 61
  • 2
  • When did you enable the archive on the table in question? If you enabled it less than one month ago, that is why. If you enabled it within a day or so ago, and you have a huge amount of data, Oracle may not have finished populating the FBDA. – Mark Stewart Sep 24 '19 at 16:09
  • I enabled 2 months before. And even if not, i wonder why cannot i access last 2 days data. i can access sysdate-1 data but not sysdate-2 – mediocre_10 Sep 25 '19 at 12:28
  • Strange. I asked if recently since I just started testing flashback archive for the first time, and was getting errors the next day, since Oracle had not yet finished building the needed tables. – Mark Stewart Sep 25 '19 at 15:47
  • Ya that doesn’t seem to be the case – mediocre_10 Sep 26 '19 at 05:07

0 Answers0