RE: Tbs READ ONLY and Snapshot too old

From: Singer, Phillip (P.W.) <psinger1_at_ford.com>
Date: Thu, 17 Jun 2004 12:34:45 -0400
Message-ID: <A45063A7D336504580F0161CEB7FEBE201AE78F6@na1fcm60.dearborn.ford.com>

Actually, you are both right.

Delayed Block Cleanout can cause an ORA-1555, even in a read only tablespace.

However, if the tablespace has been read only "for a long enough period of time", every query against it will realize that all updates to objects in that tablespace have been committed, and will never try to reconstruct the table.

As it happens, I have never had an ORA-1555 on a table which is (was?) being updated during the query; I've had lots of them due to delayed block cleanout. This was my first thought when I read the original post, however, as I read the OP, I think that the errors are appearing after the tablespace has been read only for several days.

If the time sequence is:

Load data
Make tablespace read only
Error appear for a while
Then they never appear until the next iteration

then the cause is almost certainly delayed block cleanout. FWIW, I have found that several hours may not be enough time for everything to settle down.

As a test, (if the OP is still reading, and my time sequence is spot on), try computing all statistics on all objects in the tablespace (not estimate, full compute). This will visit every block on all tables and indexes (if any are in the tablespace) and should clean out all the blocks.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark Sent: Thursday, June 17, 2004 12:18 PM
To: oracle-l_at_freelists.org
Subject: RE: Tbs READ ONLY and Snapshot too old

Actually, you can get an ORA-1555 on an object in a read only tablespace, believe it or not: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:895410916429

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Justin Cave Sent: Friday, June 11, 2004 6:15 AM
To: oracle-l_at_freelists.org
Subject: RE: Tbs READ ONLY and Snapshot too old

ORA-01555 indicates that Oracle was attempting to reconstruct the state of a block and the earlier state was not available in the undo segments. By definition, you cannot get an ORA-01555 error when trying to access a read-only object.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Jun 17 2004 - 11:31:52 CDT