Skip to Main Content
  • Questions

  • ORA-08180 While trying to select past data

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Muhammad Riaz.

Asked: July 02, 2012 - 3:17 am UTC

Last updated: February 15, 2021 - 3:35 pm UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Dear Tom,

We have flashback enabled.

SQL> show parameter  undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS2


while trying to select the past data, we get error:

SQL> /
create table t1 as select * FROM t as of timestamp TO_TIMESTAMP('25-06-2012 04:00:00','dd-mm-yyyy hh:mi:ss PM')
*
ERROR at line 1:
ORA-08180: no snapshot found based on specified time

How we can find how far we can go back?

When we see the date/time on OEM (Database Control) under "High Availability", it shows as:

Flashback Time Jun 23, 2012 7:50:56 PM

What does this date/time means?

regards,

and Tom said...

this is an error mapping the time back to an SCN. 

ops$tkyte%ORA11GR2> !oerr ora 8180
08180, 00000, "no snapshot found based on specified time"
// *Cause: Could not match the time to an SCN from the mapping table.



we keep about 5 days of uptime work of timestamps to SCN's. If you go beyond that, we might not be able to map the timestamp to an SCN.

Flashback query based on undo (not total recall in 11g) has a theoretical limit of a few days (like five days would be A LOT - a ridiculous amount actually).

If you want to flashback further in the past AND you still have the undo - then you can use the SCN based approach instead of a timestamp.



that flashback time is for flashback database, that says - based on the size of your fast recovery area, you could flashback the entire database to Jun 23rd. It isn't talking about flashback query.

You have flashback query set up to try (try - attempt) to flashback 1 hour only. Undo retention is specified in seconds:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams251.htm

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Flashback Query

Pavan Kumar Tattala, February 06, 2013 - 12:59 am UTC

Hi Tom,
My Question is Can Flashback Query Work for DDL Statements?
Tom Kyte
February 06, 2013 - 1:53 pm UTC

that doesn't even make sense.

what do you mean, what would it mean for flashback QUERY to work for a DDL statement?


(flashback query does not work OVER ddl - if you do ddl to some table, you cannot flashback query back over that DDL (with special exceptions for flashback data archive...))

ORA-08180: no snapshot found based on specified time

ojock, February 12, 2021 - 9:12 am UTC

Can we silent those ORA-08180 errors ?

if the table has Flashback Data Archive Enabled with a retention of 12 months, but I'm want to query all versions of a row in month 6 (or at any time before 12 months is up) with this;

select * from test_tbl 
where versions between timestamp add_months(systimestamp,-12)  and systimestamp where id = 1;


I know there's no snapshot, but I only care about getting ALL versions of a row whenever I want, without having to work out what the "begin" timestamp should be and changing my query every time.

I've been tinkering with this;

select * from test_tbl 
where versions between scn (select dropscn from sys.sys_fba_trackedtables where objname='TEST_TBL') and dbms_flashback.get_system_change_number
where id = 1;


Is that valid ? I can't find information on dropscn ?
Chris Saxon
February 12, 2021 - 10:43 am UTC

The sys_fba* tables are undocumented, so there are minimal details available on these!

I'm not sure what dropscn represents, but in any case it doesn't sound like it relates to the first available point.

You should be able to query the min/max available date with:

versions between scn minvalue and maxvalue


ORA-08180: no snapshot found based on specified time

ojock, February 12, 2021 - 1:57 pm UTC

Many thanks Chris.

Is there any differences between these 2;

versions between scn minvalue and maxvalue

and

versions between timestamp minvalue and maxvalue
Chris Saxon
February 15, 2021 - 3:35 pm UTC

Converting between and SCNs and timestamps is only an approximation, accurate within three seconds.

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SCN_TO_TIMESTAMP.html#GUID-BCB0C8EE-0E03-4A61-A41A-69975FAC1803

So you when searching for specific times - particularly in a short timeframe - you may spot some inconsistencies.

In this case, both ask for all available history, so I'm not aware of a difference. Internally this works off SCNs, so I'd tend towards using that.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.