Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ORA-01555 with Automatic Undo Management mode

Re: ORA-01555 with Automatic Undo Management mode

From: Daniel Fink <>
Date: Wed, 18 Jun 2003 14:01:13 -0700
Message-ID: <>


        How long ago was the data for the mview loaded? It is possible that you are running into the scenario where delayed block cleanout is causing the ORA-1555. Is the name of the undo segment in the error the same as the large undo extent? I would suspect that they are different.

        I have not tested mviews as transactions, but I presume oracle considers it as one large transaction. This would prevent it from acquiring new undo segments, which makes sense as there is one segment that is very large in respect to all the others. There are only 88 segments in the tablespace, both offline and online, not 88 online? Assuming the 88 segments have been shrunk to minimum (128k), they will consume only 10meg. With the numbers you quote, this sounds like the case.

        SMON awakes periodically to offline undo segments and it may have offlined a segment that was needed by the transaction (it just did not know that at the time). SMON should not offline a segment if the retention time has not been met, but I don't know this for a fact. There is a thought in the deep dark recesses of my mind that the situation may be the result of the expire time algorithm that Oracle uses. (The sound you have just heard is Kirti fleeing from the list before we go down that path again!)

        That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. If the process was attempting to acquire an extent and could not find sufficient free space, you would receive the 'Unable to extend segment' error and not the ORA-1555.

        Since you are the only process running, I think delayed block cleanout (a result of the data loading process and nothing to do with your mview) or segment offlining are your likely culprits. I can't recommend this will work, but give it a shot. Alter the tablespace containing the source table into read only mode, then run the mview statement. The fact that the ts is read-only guarantees that all data contained within has been committed. This solution was suggested to me by a very wise member of
the list, so it is worth a try.

Daniel Fink

BTW, AUM is not a requirement for Flashback Query. However, it is the only configuration Oracle will support for FBQ and it is more likely to succeed with AUM.

Thomas Day wrote:
> I'm trying to create a materialized view on a 7G table. I'm using UNDO and
> undo_retention=108000, which if I understand correctly is 30 hours. I have
> 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get
> ORA-01555: snapshot too old: rollback segment number with name "" too
> small
> The FM says:
> ORA-01555 snapshot too old: rollback segment number string with name "
> string" too small
> Cause: Rollback records needed by a reader for consistent read are
> overwritten by other writers.
> Action: If in Automatic Undo Management mode, increase the setting of
> UNDO_RETENTION. Otherwise, use larger rollback segments.
> UNDOTBS1 still had 3G of space free with about 88 active undo extents, the
> largest was 4,154,458,112 bytes. Was it trying to create another 4G
> extent? Is there something I am missing? This Automatic stuff doesn't
> seem to be so automatic. I can create this using rollback segments but I
> wanted to use UNDO because it allows past point in time queries.
> Oracle
> Win2K.
> Any thoughts (besides get a real operating system and use rollback
> segments) would be greatly appreciated.
> --
> Please see the official ORACLE-L FAQ:
> --
> Author: Thomas Day
> 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: [EMAIL PROTECTED] (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).begin:vcard
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
fn:Daniel Fink
end:vcard Received on Wed Jun 18 2003 - 16:01:13 CDT

Original text of this message