Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tbs READ ONLY and Snapshot too old

RE: Tbs READ ONLY and Snapshot too old

From: Goulet, Dick <>
Date: Fri, 11 Jun 2004 10:38:15 -0400
Message-ID: <>


    If you look at the error your getting and the table/tablespace setup you've described as well as the processing environment, it is immediately obvious that the database is NOT the problem, but the application. What you might do to band-aid their application is to take those small tables and turn them into global temporary tables. The real problem here is that the application is creating, modifying, and deleting data that other sessions are have an interest in. Couple that with those massive tables that you've mentioned and getting an ORA-01555 under these circumstances is a guarantee. What is happening that I believe your duhvelopers can understand is that one session is modifying a block, not an insert in most cases, and has issued a commit. Now that block is resident in the SGA as a dirty block, but either the current session or another session is requesting data from those massive tables. So Oracle needs a place to put new data blocks and that modified one is low on the LRU list, so off to disk it goes & the rollback information heads for the trash. Another session now comes along to either modify or read that block, with an earlier SCN, and bingo, no rollback available, ORA-01555.  

    Therefore, possible band-aids:  

  1. Make the smaller tables Global Temps
  2. Increase the db_block_buffers or their 9i equivalent.
  3. If your on 9i or better increase Undo retention to some insane value like 24 hours. BTW: Increase your UNDO tablespace by a factor of 100. (That ought to get damanagements attention.)
  4. Scan Metalink for ORA-01555. Note:269814.1 should be of help.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

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

From: NGUYEN Philippe (Cetelem) [] Sent: Friday, June 11, 2004 7:03 AM
To: ''
Subject: RE: Tbs READ ONLY and Snapshot too old

thank Justin,
Errors occurs during the month but we have a very bad application that generate sql scripts with a lot of cursors opened on thoses big tables ( more than 20Go per tables) thoses script create small tables (10 - 200 Mo) with datas pick up from the different big tables. The problem is that the generated script are often launch in the same time and I we can't touch the code.  

Philippe Nguyen
CETELEM - Administration, Architecture Décisionnelle Direction Customer Relationship Management E-Mail :
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88

-----Message d'origine-----

De : []De la part de Justin Cave Envoyé : vendredi 11 juin 2004 12:15
À :
Objet : 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.  

Do you get these ORA-01555 errors shortly after doing the load or do you get them throughout the day? The only thing I can think is that these errors are caused by delayed block cleanout, but that should take care of itself soon after the load. If you get errors throughout the month, I would suspect that the big tables aren't the cause.  

Justin Cave
Distributed Database Consulting, Inc. <> /askDDBC  

From: [] On Behalf Of NGUYEN Philippe (Cetelem) Sent: Friday, June 11, 2004 4:15 AM
Subject: Tbs READ ONLY and Snapshot too old

Hi List,
just a little question about read only tbs : We have big tables that are loaded once and never updated during the month , so I think it's a good thing to switch their tbs to read only mode. Web have a lot of script that request thoses heavy tables to build small tables and we often encounter ORA-01555. Do you think that "in general" this operation could enhance our ORA-01555 problems ? TIA

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Received on Fri Jun 11 2004 - 09:36:25 CDT

Original text of this message