I have a table which is flashback enabled. I want to query the data using AS OF clause as following:
SELECT *
FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2019-09-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
The flashback archive has a retention period of 14 days.
As of today, on 16th Oct 2019, I am able to go back up till 11th Sept 2019. If I pass 10th Sept, that is 2019-09-10 00:00:00 into TO_TIMESTAMP in the above query, I am getting ORA-08180: no snapshot found based on specified time
So, how can I find the exact time till when the SCN is retained? I went through OraDocs, and I found the following info:
The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours.
I had queried for UNDO_RETENTION parameter and it is set to default 900 seconds. And there is only one flashback archive present, with RETENTION_DAYS set to 14 DAYS. So, according to the doc, the life of SCN must be 14 days. But how am I able to go back around a month back?
P.S: I was checking on another database where UNDO_RETENTION parameter is set to default 2700 (45 min) and has only one flashback archive with RETENTION_DAYS set to 14. In that case, I was able to query back up to just 6 days. How exactly is the life of SCN calculated? Does it has anything to do with when the flashback archive is created?