| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tbs READ ONLY and Snapshot too old
Philippe,
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:
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: NGUYEN Philippe (Cetelem) [mailto:philippe.nguyen_at_cetelem.fr]
Sent: Friday, June 11, 2004 7:03 AM
To: 'oracle-l_at_freelists.org'
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.
-----Message d'origine-----
De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]De la part de Justin Cave
Envoyé : vendredi 11 juin 2004 12:15
À : oracle-l_at_freelists.org
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.
http://www.ddbcinc.com <http://www.ddbcinc.com/askDDBC> /askDDBC
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of NGUYEN Philippe (Cetelem)
Sent: Friday, June 11, 2004 4:15 AM
To: oracle-l_at_freelists.org
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
Philippe
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Fri Jun 11 2004 - 09:36:25 CDT
-----------------------------------------------------------------
 |
 |