Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Flashback Query - Sanpshot Too Old Error
Prem K Mehrotra wrote:
> "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>... >>> >>>>"Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message >>>>news:43441e77.0411181346.371091e5_at_posting.google.com... >>>> >>>>>premmehrotra_at_hotmail.com (Prem K Mehrotra) wrote in message >> >> 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? >>>>> >>>>>rem >>>> >>>>Your UNDO tablespace is too small >>>> >>>>Matthias >>> >>>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 don't think that this is the case. From memory, if RETENTION is not set, then PCTVERSION is used to retain information for read-consistency purposes. And clearly, your PCTVERSION is too small.
If RETENTION is set, then effectively the UNDO_RETENTION parameter applies.
Regards
HJR
>
> I don't see anything wrong in syntax of my flashback query?
Received on Sat Nov 20 2004 - 20:48:19 CST