Re: ORA-01555 with Automatic Undo Management mode

>>Are you certain that the 1555 was caused by extent stealing?

Nope, not certain at all.

>>> [EMAIL PROTECTED] 06/18/03 09:54PM >>>
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:



>>>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.

>  

>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
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).