ORA-08180: no snapshot found based on specified time

Ankan Chanda

Hi team,

As I am using certain flashback queries on a table to retrieve certain data, so for example, I have used one of these:

select * from service_req_prod versions between timestamp

to_timestamp('2021-07-03 09:22:33', 'YYYY-MM-DD HH:MI:SS') 

and

to_timestamp('2021-07-04 09:22:33', 'YYYY-MM-DD HH:MI:SS');

So, the above query is giving me the error:

ORA-08180: no snapshot found based on specified time

What does the above error mean? Does it mean that there was not any data present during that timeline? I am certain that I did lose 1-2 rows of data, but like the above queries , I have tried other queries as well but its showing the same error. Is there any problem with the query? How should I approach this issue?

Thanks and regards

Ankan

AndyH

The error is saying that Oracle doesn't know what SCNs represent the timestamps you've given it.

As far as I'm aware, Oracle only keeps a few days worth of timestamp to SCN relationships so any flashback queries before then require you to use the SCN-based versions query format.

Ankan Chanda

Hi AndyH,

Thanks for your response. Can you help me with any documentation regarding SCN-based version query format? As far as I know the SCN is assigned AFTER THE COMMIT. I did not write down any COMMIT command. So where can I grab the SCN and make the necessary changes. How to deal with this issue?