I looked through metalink (doc 40689.1) and I don't see how the delayed
block cleanout scenario could happen (in my case) given that there was only
one transaction running.
I'm still stuck with that last 4g block that was allocated with only 3G of
free space remaining. Also, that filespace was not autoextensible.
SQL> select * from dba_data_files where tablespace_name = 'UNDOTBS1';
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS
STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------------- ----------
--------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS
---------- -----------
D:\ORACLE92\ORADATA\EID9SNP\UNDOTBS01.DBF
2 UNDOTBS1 209,715,200 25600
AVAILABLE 2 YES 3.4360E+10 4194302 640
209649664 25592
D:\ORACLE92\ORADATA\EID9SNP\UNDOTBS02.DBF
25 UNDOTBS1 7,340,032,000 896000
AVAILABLE 25 NO 0 0 0
7339966464 895992
SQL> select tablespace_name, initial_extent, next_extent, status,
extent_management, allocation_type, segment_space_management
2 from dba_tablespaces where tablespace_name = 'UNDOTBS1';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT STATUS
EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ -------------- ----------- ---------
---------- --------- ------
UNDOTBS1 65536 ONLINE LOCAL
SYSTEM MANUAL
The snapshot built just fine (7G in 9.5 hrs) using rollback segments -
RBS_LARGE below.
Map of Tablespace rbs2
File
OWNER OBJECT Id BLOCK_ID BLOCKS
BYTES
---------- --------------------------- ---- ---------- ----------
----------------
SYS RBS_LARGE 24 9 25600
209,715,200
SYS RBS_LARGE 24 25609 25600
209,715,200
SYS RBS_LARGE 24 51209 25600
209,715,200
SYS RBS_LARGE 24 76809 25600
209,715,200
SYS RBS_LARGE 24 102409 25600
209,715,200
SYS RBS_LARGE 24 128009 25600
209,715,200
SYS RB3 24 153609 25600
209,715,200
SYS RB3 24 179209 25600
209,715,200
You can't argue with success and we're out of time so automatic undo bites
(bytes) the dust on this database. I haven't given up on it, I just don't
have enough time to make it work here.
"Darrell
Landrum" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<dlandrum cc:
@zalecorp.com> Subject: Re: ORA-01555 with Automatic
Undo Management mode
Sent by:
ml-errors
06/19/2003 08:44
AM
Please respond
to ORACLE-L
>>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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
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).
