Re: Flashback Query - Sanpshot Too Old Error

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 21 Nov 2004 13:48:19 +1100
Message-ID: <41a001f2$0$31432$afc38c87@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?
Received on Sat Nov 20 2004 - 20:48:19 CST