RE: ORA-01555 Mystery (Help)

2002-01-27 Thread
: Sat, January 26, 2002 1:11 AM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01555 Mystery (Help) Jared: The table was analyzed (via estimate) Wednesday night. I don't know if it was before or after Wednesday night's attempt at the extract but if the blocks are getting

RE: ORA-01555 Mystery (Help)

2002-01-27 Thread Sinard Xing
1:11 AM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01555 Mystery (Help) Jared: The table was analyzed (via estimate) Wednesday night. I don't know if it was before or after Wednesday night's attempt at the extract but if the blocks are getting cleaned out via

ORA-01555 Mystery (Help)

2002-01-25 Thread Walter K
Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Charlie Mengler
In Tom Kyte's book Expert 1-on-1 he says to ANALYZE the table BEFORE starting a big query. Read Chapter 5, starting on page 185 for a complete explanation. Walter K wrote: Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Gogala, Mladen
Don't believe your users. Somebody is causing oracle to read blocks in consistent mode, ie. reading them from rollback segments. If the user is right, then try locking the participating tables in the exclusive mode and see who will complain. Alternatively, go to V$ACCESS table, see who is

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Baker, Barbara
! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Rajesh . Rao
Is it a simple select statement, or is it a cursor select in an PL/SQL block? Does her transaction itself perform any DML on those tables? Raj Walter K [EMAIL PROTECTED]@fatcity.com on 01/25/2002 11:15:26 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To:

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Rajesh . Rao
overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Paul Baumgartel
that the rollback segments don't get overwritten. Good luck! Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 9:15 AM To: Multiple recipients of list ORACLE-L Subject:ORA-01555 Mystery (Help) Hi

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Stephane Faroult
Subject: ORA-01555 Mystery (Help) Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table

RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Baker, Barbara
Subject:Re:RE: ORA-01555 Mystery (Help) Barb, I've tried Steve's idea in the past and although it sorta fixed the problem with the large batch job, it created problems elsewhere. It also did not totally fix the problem when other applications updated parts

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Jared . Still
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Walter K
:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked

RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Kathy Duret
the grief and find a resolution. Barb -- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 11:09 AM To: Baker; Barbara; Multiple recipients of list ORACLE-L Subject:Re:RE: ORA-01555 Mystery (Help

RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Jeremiah Wilton
On Fri, 25 Jan 2002, Kathy Duret wrote: How about doing a set transaction to a large rollback before running this query if the analyze doesn't resolve the problem. That will have no effect. http://www.speakeasy.org/~jwilton/oracle/snapshot-too-old.html -- Jeremiah Wilton

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Walter K
. Jared Paul Baumgartel [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 09:30 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Sure, but the original

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Jared . Still
: Subject:Re: ORA-01555 Mystery (Help) I was almost ready to subscribe to the idea of delayed cleanout, but I cannot understand why really. The necessity for reading a block from the rollback segments comes from encountering during the course of the SELECT a block the SCN of which is higher

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Jared . Still
compute the stats. But now I'm speculating. :) Jared Walter K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 11:20 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help

Re:RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread dgoulet
; Barbara; Multiple recipients of list ORACLE-L Subject:Re:RE: ORA-01555 Mystery (Help) Barb, I've tried Steve's idea in the past and although it sorta fixed the problem with the large batch job, it created problems elsewhere. It also did not totally fix

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Baker, Barbara
][SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 3:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01555 Mystery (Help) I don't have a definitive answer for that. My guess would be that 'compute' would be required so

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Walter K
, January 25, 2002 12:30 PM To: Multiple recipients of list ORACLE-L Subject:RE: ORA-01555 Mystery (Help) Another fact, that should be mentioned, is that the table in question was built (loaded) two days ago. The nightly ETL processes for the warehouse are pretty substantial

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Nick Wagner
Title: RE: ORA-01555 Mystery (Help) would Set transaction read only help here? -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 2:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01555 Mystery (Help) I think you

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Rachel Carmichael
] Sent by: [EMAIL PROTECTED] 01/25/02 10:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: ORA-01555 Mystery (Help) I was almost ready to subscribe to the idea of delayed cleanout, but I

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Deshpande, Kirti
the stats. But now I'm speculating. :) Jared Walter K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/25/02 11:20 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: ORA-01555 Mystery (Help) Jared