Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Flashback Query - Sanpshot Too Old Error
"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<KNund.768$AL5.367_at_twister.nyroc.rr.com>...
> "Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0411190717.376aded7_at_posting.google.com... > > "Access" <idmwarpzone_NOSPAM__at_yahoo.com> wrote in message news:<419d1e62$0$30690$ba620e4c_at_news.skynet.be>...
> news:<43441e77.0411171713.1e148417_at_posting.google.com>...> > > >
> > > > > I am using Oracle 9i, 9.2.0.5 on HP UNIX 11.0. I have 2 GB of UNDO
> > > > > table
> > > > > space. When I do a flashback query, I get Snapshot too old error:
> > > > >
> > > > > 1 create table wreports_que_flash
> > > > > 2* as select * from wreports_que as of
> > > > > timestamp(to_timestamp('17-NOV-2004 00:00:00', 'DD-MON-YYYY
> > > > > HH24:MI:SS'))
> > > > > SQL> /
> > > > > as select * from wreports_que as of
> > > > > timestamp(to_timestamp('17-NOV-2004 00:00:00', 'DD-MON-YYYY
> > > > > HH24:MI:SS'))
> > > > > *
> > > > > ERROR at line 2:
> > > > > ORA-01555: snapshot too old: rollback segment number with name "" too
> > > > > small
> > > > > ORA-22924: snapshot too old
> > > > >
> > > > > I do not have much database activity. My undo_retention = 604800
> > > > > which is seven days.
> > > > >
> > > > > I have done flashback queries in the past with success. I don't know
> > > > > why I am getting this error when I am only accessing a few hours ago
> > > > > of data.
> > > > >
> > > > > Any ideas will be helpful.
> > > > >
> > > > > Thanks a lot,
> > > > >
> > > > > Prem
> > > >
> > > > Any ideas?
> > >
> > >
> > > > Not really, my undo tablespace is 1800M and only 2.3% of the space is used. > > Reason must be something else. > > > > Prem > > Does the table you are selecting from have a CLOB/BLOB field ? > If yes then what is the retention period for that? > > Anurag
I now found renetion parameter for CLOB's. Since I did not specify any value, default value of undo_rention is used. So, it is still a mystery why I am getting snapshot too old error?
I don't see anything wrong in syntax of my flashback query? Received on Sat Nov 20 2004 - 16:50:37 CST