Re: Flashback Query - Sanpshot Too Old Error

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Sat, 20 Nov 2004 16:47:46 GMT
Message-ID: <SyKnd.1465$AL5.912@twister.nyroc.rr.com>

"Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0411200525.73729a20_at_posting.google.com...

> "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
>
>
> Anurag:
>
> That may be it. My table does have a CLOB. How does one set a
> retention for CLOB (my google search has not yet found that).
>
> Prem

Prem,

Read the doco. I'm saying this because the way the retention is kept for CLOBS/BLOBS has changed over versions.

As far as I remember: If you set PCTVERSION, then that is respected. You can also set RETENTION parameter (or that parameter defaults to the undo_retention time). If you have set PCTVERSION, then either try unsetting it and go with the RETENTION parameter ... or set PCTVERSION to a higher value like 30% / 50% / 100% ... whichever works.

PCTVERSION is the default if you are not using AUM. RETENTION (equal to undo_retention) is the default in AUM. You can set PCTVERSION even if you are using AUM, but you cannot use RETENTION is you are not using AUM.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2128987

(above is a doco link to the 9.2 SQL reference manual)

Your error is most probably related to the LOBs because of two reasons:

No rollback segment name was provided in the error. Points to a typical LOB retention problem because LOB's undo is not retained in the rollback segments.
The error was accompanied by ORA-22924 .. which is typical when its LOB related.

Good luck,

Anurag Received on Sat Nov 20 2004 - 10:47:46 CST