Re: Flashback Query - Sanpshot Too Old Error

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<41a001f2$0$31432$afc38c87_at_news.optusnet.com.au>...
> 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 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 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?

I still do not know whether Oracle uses pctversion or retention,

1. when I specify pcversion 15 in create table statement, oracle shows: pctversion 15 retention 604800
2. When I specify RENETION in create table statement, oracle shows pctversion 10 retention 604800
3. When I do not specify anything in create table related to clob, oracle shows pctversion 10 retention 604800

I am looking into user_lobs

I am also not getting flashback error any more.

I am using segment space management auto by mistake (without realizing). I see some bugs related to this with lobs, so it is possincle clob got corrupted or so.

Prem Received on Mon Nov 22 2004 - 11:25:29 CST