Forum Stats

  • 3,816,002 Users
  • 2,259,128 Discussions
  • 7,893,362 Comments

Discussions

ORA-08180: no snapshot found based on specified time

Ankan Chanda
Ankan Chanda Member Posts: 32 Green Ribbon

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

Tagged:

Answers

  • AndyH
    AndyH Member Posts: 804 Bronze Trophy

    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
    Ankan Chanda Member Posts: 32 Green Ribbon
    edited Aug 18, 2021 6:12AM

    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?