Re: ORA-01555 with Automatic Undo Management mode

Darrell,
    The space management algorithm is such that free extent acquisition is
the second method for undo allocation. The first is claiming expired extents from the current segment. With a low retention setting, this is entirely possible. Are you certain that the 1555 was caused by extent stealing?
    What I was trying to say is that a segment will extend by grabbing free
(unallocated) extents before it allocates extents from other segments (stealing). If the mview creation is a single transaction, it is likely that the extents in the large undo segment are all still active and cannot be reused, no matter what (active undo is NEVER overwritten).

-- 
Daniel W. Fink
http://www.optimaldba.com

Darrell Landrum wrote:
<blockquote type="cite"
 cite="">
  
    
      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. <<

Daniel,
Are you saying this correctly?  The reason I ask, is I've seen a 1555
error in a system with one job running, only 480 MBs of undo space used,
10 minute retention setting, and an undo tablespace of 12 GB.  It
doesn't seem that Oracle grabbed an extent from free space before using
an expired one.  (Retention setting is now much, much higher.  Got to
watch those hours to seconds conversions.)





  
  
    
      
        [EMAIL PROTECTED] 06/18/03 03:19PM >>>
        
      
    
  
  Thomas,
	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.