All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: Tbs READ ONLY and Snapshot too old

Singer, Phillip (P.W.)


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:
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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Bobak, Mark
Sent: Thursday, June 17, 2004 12:18 PM
To: oracle-l@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:

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Justin Cave
Sent: Friday, June 11, 2004 6:15 AM
To: oracle-l@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.