Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rollback and what table's DML caused an ORA-1555?

RE: Rollback and what table's DML caused an ORA-1555?

From: Jesse, Rich <>
Date: Fri, 04 Apr 2003 13:48:40 -0800
Message-ID: <>

Excellent advice, Gaja! Yes, we do have OPTIMAL set. From the docs I don't see a way to remove the OPTIMAL setting once used. I assume this means new RBSs?


Rich Jesse                        System/Database Administrator           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Friday, April 04, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L


The overcommiting is a definite suspect and is worth looking into. ALso, do you by chance have OPTIMAL set on your rollback segments? If so, I'd suggest you remove the OPTIMAL clause and try again. In my experience, I have had my share of hassles with OPTIMAL. Even when it was sized 'reasonably large' (way above INITIAL * MINEXTENTS) for the application.

OPTIMAL does increase the probability of ORA-1555, as extents of your rollback segments that have the "before images" of your transactions, can get dropped, while your queries are left "high and dry". Just something to check.

The downside of not setting OPTIMAL is more disk usage for your rollback segments. In the bigger scheme of things, it may be much cheaper to eat the disk cost (which is probably a few dollars) than to deal with queries failing with ORA-1555s. If you are so strapped for space, then you can "hand shrink" the rollback segments using the ALTER rollback segment xxx SHRINK command via a job, at a time when no queries are running.

If you don't have OPTIMAL set, and if you are using any READ-ONLY tablespaces that were put in READ-ONLY mode recently, then try the following:

  1. Set those tablespaces back to READ-WRITE mode
  2. Write a sql-generating-sql script that performs a select count(*) on all the objects in the tablespace
  3. Set the tablespaces back to READ-ONLY.

The above exercise will force a block-cleanout (if required) on all the objects, which may not have occured before the tablespace was originally put in READ-ONLY mode.

Hope this helps,


Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Apr 04 2003 - 15:48:40 CST

Original text of this message