2

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?

ravioli
  • 3,749
  • 3
  • 14
  • 28
Ravi Shankar
  • 277
  • 9
  • 23
  • I believe, by flashback enabled table, you mean flashback archive enabled table. SCNs are associated with committed change in the database. By earliest SCN, we are looking for the earliest SCN associated with an object. To get all SCNs associated with an object that was changed and committed, say your employees table, use the below SQL. SELECT versions_startscn START_SCN, versions_endscn END_SCN FROM employees VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE; The result records are in descending chronological order, so the last record will have the earliest SCN. – ArtBajji Oct 18 '19 at 11:45
  • To get the timestamp associated with an SCN, use select scn_to_timestamp() as timestamp from dual; Once you have the SCN you may not need the timestamp as the SCN is sufficient to query the Flashback Data Archive. – ArtBajji Oct 18 '19 at 11:46

0 Answers0