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 (See ORA-1555.ora-code.com) 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:
1) Load data
2) Make tablespace read only
3) Error appear for a while
4) 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@(protected) [mailto:oracle-l-bounce@(protected)]On Behalf Of Bobak, Mark
Sent: Thursday, June 17, 2004 12:18 PM
To: oracle-l@(protected)
Subject: RE: Tbs READ ONLY and Snapshot too old
Actually, you can get an ORA-1555 (See ORA-1555.ora-code.com) 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@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Justin Cave
Sent: Friday, June 11, 2004 6:15 AM
To: oracle-l@(protected)
Subject: RE: Tbs READ ONLY and Snapshot too old
ORA-01555 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com) error when trying to access a read-only object.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<TITLE >Message </TITLE >
<META content= "MSHTML 6.00.2800.1276 " name=GENERATOR > </HEAD >
<BODY >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >Actually,
you are both right. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >Delayed
Block Cleanout can cause an ORA-1555 (See ORA-1555.ora-code.com), even in a read only
tablespace. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >However, if
the tablespace has been read only "for a long enough period of
time ", </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >every query
against it will realize that all updates to objects in that tablespace
have </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >been
committed, and will never try to reconstruct the table. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >As it
happens, I have <STRONG > never </STRONG > had an ORA-1555 (See ORA-1555.ora-code.com) on a table which is
(was?) being </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >updated
during the query; I 've had lots of them due to delayed block
cleanout. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >This was my
first thought when I read the original post, however, as I read the
OP, </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >I think that
the errors are appearing after the tablespace has been read
only </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >for several
days. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >If the time
sequence is: </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >1)
Load data </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >2)
Make tablespace read only </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >3)
Error appear for a while </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >4)
Then they never appear until the next iteration </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >then the
cause is almost certainly delayed block cleanout. FWIW, I have
found </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >that several
hours may not be enough time for everything to settle down. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 > </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >As a test,
(if the OP is still reading, and my time sequence is spot on),
try </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >computing
all statistics on all objects in the tablespace (not estimate,
full </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN
class=464542316-17062004 >compute). This will visit every block on all
tables and indexes (if any are </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > <SPAN class=464542316-17062004 >in the
tablespace) and should clean out all the blocks. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Tahoma color=#008000 > </FONT > </DIV >
<BLOCKQUOTE dir=ltr
style= "PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #008000 2px solid; MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Original Message-- -- <BR > <B >From: </B >
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B >On
Behalf Of </B >Bobak, Mark <BR > <B >Sent: </B > Thursday, June 17, 2004 12:18
PM <BR > <B >To: </B > oracle-l@(protected) <BR > <B >Subject: </B > RE: Tbs READ ONLY
and Snapshot too old <SPAN class=464542316-17062004 > <FONT color=#008000
size=3 > </FONT > </SPAN > <BR > <BR > </FONT > </DIV >
<DIV > <SPAN class=378121716-17062004 > <FONT face=Arial color=#0000ff
size=2 >Actually, you can get an ORA-1555 (See ORA-1555.ora-code.com) on an object in a read only
tablespace, believe it or not: </FONT > </SPAN > </DIV >
<DIV > <SPAN class=378121716-17062004 > <FONT face=Arial color=#0000ff size=2 > <A
href= "http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:895410916429 " >http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:895410916429 </A > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=378121716-17062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=378121716-17062004 > <FONT face=Arial color=#0000ff
size=2 >-Mark </FONT > </SPAN > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV >
<DIV > <FONT face=Tahoma size=2 > <BR > <B >From: </B > oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B >On Behalf Of </B >Justin
Cave <BR > <B >Sent: </B > Friday, June 11, 2004 6:15 AM <BR > <B >To: </B >
oracle-l@(protected) <BR > <B >Subject: </B > RE: Tbs READ ONLY and Snapshot too
old <BR > <BR > </FONT > </DIV >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 >ORA-01555 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com) error
when trying to access a read-only object. </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 > </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > </DIV > </BLOCKQUOTE > </BLOCKQUOTE > </BODY > </HTML >
