| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ORA-01555 with Automatic Undo Management mode
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 BLOCKSSTATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------------- ------------------- ------------ --- ---------- ---------- ------------ USER_BYTES USER_BLOCKS
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 STATUSEXTENT_MAN ALLOCATIO SEGMEN
UNDOTBS1 65536 ONLINE LOCALSYSTEM 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
---------- --------------------------- ---- ---------- ----------209,715,200
----------------
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
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 servicesReceived on Thu Jun 19 2003 - 10:16:24 CDT
---------------------------------------------------------------------
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).
 |
 |