Tbs READ ONLY and Snapshot too old 2004-06-18 - By Binley Lim
MessageActually, you are both right.
[BL] They cannot both be right -- either you get ORA-1555 (See ORA-1555.ora-code.com) on a READ ONLY tablespace or you do not.
Delayed Block Cleanout can cause an ORA-1555 (See ORA-1555.ora-code.com), even in a read only tablespace.
[BL] Agreed.
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.
[BL] Disagree. What is "long enough " and how can a query "realise " something? Problem is the ITL entries which tell the query to visit the rollback segments cannot be cleaned out because they are in a READ ONLY tablespace. Every time the block(s) are queried, they repeat the same exercise. This problem is magnified when you do selective restores of tablespaces without the rollback segments, and find you cannot read from those tablespaces without the rollback tablespaces. Also, you don 't reconstruct the table, but just reconstruct the individual blocks.
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.
[BL] A table being updated is guaranteed to have its undo entries intact.Since this is the only way the rollback can happen if the update does not commit. Your query will find what it needs in the rollback segments, and not get an ORA-1555 (See ORA-1555.ora-code.com), unless the update has already committed, or rolled-back.
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.
[BL] Much easier to simply do "select max(column)... " on an un-indexed column to cause FTS to cause cleanouts. Statistics is a bit of an overkill.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD > <TITLE >Message </TITLE >
<META http-equiv=Content-Type content= "text/html; charset=iso-8859-1 " >
<META content= "MSHTML 5.50.4522.1800 " name=GENERATOR >
<STYLE > </STYLE >
</HEAD >
<BODY bgColor=#ffffff >
<DIV > <FONT face=Arial size=2 >Actually, you are both right. </FONT > </DIV >
<DIV > <FONT face=Arial size=2 > </FONT > </DIV >
<DIV > <FONT face=Arial size=2 >[BL] They cannot both be right -- either you get
ORA-1555 (See ORA-1555.ora-code.com) on a READ ONLY tablespace or you do not. </DIV >
<DIV > <BR >Delayed Block Cleanout can cause an ORA-1555 (See ORA-1555.ora-code.com), even in a read only
tablespace. </DIV >
<DIV > </DIV >
<DIV >[BL] Agreed. <BR > <BR >However, if the tablespace has been read only
"for a long enough period of time ", <BR >every query against it will realize that
all updates to objects in that tablespace have <BR >been committed, and will never
try to reconstruct the table. </DIV >
<DIV > </DIV >
<DIV >[BL] Disagree. What is "long enough " and how can a query "realise "
something? Problem is the ITL entries which tell the query to visit
the rollback segments cannot be cleaned out because they are in a READ ONLY
tablespace. Every time the block(s) are queried, they repeat the same exercise.
This problem is magnified when you do selective restores of tablespaces without
the rollback segments, and find you cannot read from those tablespaces without
the rollback tablespaces. Also, you don 't reconstruct the table, but just
reconstruct the individual blocks. <BR > <BR >As it happens, I have never
had an ORA-1555 (See ORA-1555.ora-code.com) on a table which is (was?) being <BR >updated during the query;
I 've had lots of them due to delayed block cleanout. <BR >This was my first
thought when I read the original post, however, as I read the OP, <BR >I think
that the errors are appearing after the tablespace has been read only <BR >for
several days. <BR > </DIV >
<DIV >[BL] A table being updated is guaranteed to have its undo entries
intact.Since this is the only way the rollback can happen if the update does not
commit. Your query will find what it needs in the rollback segments, and not get
an ORA-1555 (See ORA-1555.ora-code.com), unless the update has already committed, or rolled-back. </DIV >
<DIV > </DIV >
<DIV >As a test, (if the OP is still reading, and my time sequence is spot on),
try <BR >computing all statistics on all objects in the tablespace (not estimate,
full <BR >compute). This will visit every block on all tables and indexes
(if any are <BR >in the tablespace) and should clean out all the blocks. <BR > </DIV >
<DIV >[BL] Much easier to simply do "select max(column)... " on an un-indexed
column to cause FTS to cause cleanouts. Statistics is a bit of an
overkill. </DIV > </FONT > </BODY > </HTML >
|
|
