Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Flashback Query - Sanpshot Too Old Error

Re: Flashback Query - Sanpshot Too Old Error

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 19 Nov 2004 06:53:17 -0800
Message-ID: <92eeeff0.0411190653.64dc1a5a@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

UNDO tablespace size should be set according to the undo_retention value. The greater the undo_retention value, the more space needs to be allocated in UNDO tablespace to hold that undo.

I can't hazard a guess because it all depends on your database activity/users/transactions etc.

Regards
/Rauf Received on Fri Nov 19 2004 - 08:53:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US