Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reusing UNEXPIRED UNDO blocks
Stephen,
Several questions come to mind.
1) Is the autoextended datafile at/near it's limit? 2) How many undo segments do you have? 3) What version of Oracle? 4) Is undo_retention guaranteed?
In terms of expired/unexpired, it is supposed to mark the block as expired after the last commit time + undo_retention > current time. However, in practice, this is usually not the case. In testing, I (and others) have seen blocks expire almost immediately after a commit and other blocks not expire even days after the last commit time + undo_retention.
If a block is not being used by a current transaction, not part of the undo segment minimum, unexpired and undo_retention is not guaranteed, a current transaction should steal it (and you can see that stealing is occurring in the query you ran).
Regards,
Daniel Fink
Stephen Barr <ascaroth969_at_yahoo.co.uk> wrote: When do oracle reuse an unexpired undo block?
We have a situation where there are four datafiles associated with the UNDO tablespace -
READONLY_at_MIDDWHP>select file_id, autoextensible
2 from dba_data_files
3 where tablespace_name = 'UNDOTBS1'
4 /
FILE_ID AUT
---------- ---
164 NO 163 NO 162 NO 2 YES
You can see that one of the datafiles is marked as autoextensible - an oversight by the DBA's.
However, we're seeing transactions fail with ORA-01562: failed to extend rollback segment number 17.
The strange thing is that we checked just before this transaction failed and there was tons of room in the tablespace - why would this occur?
The entry in v$undostat for this time period looks like this -
BEGIN_TIME : 04-apr-2006 10:35:03 END_TIME : 04-apr-2006 10:45:03 UNDOTSN : 1 UNDOBLKS : 47115 TXNCOUNT : 272225 MAXQUERYLEN : 93649 MAXQUERYID : frma9q6tqbuwd MAXCONCURRENCY : 11 UNXPSTEALCNT : 8 UNXPBLKRELCNT : 516 UNXPBLKREUCNT : 0 EXPSTEALCNT : 1663 EXPBLKRELCNT : 279212 EXPBLKREUCNT : 0 SSOLDERRCNT : 0 NOSPACEERRCNT : 2 ACTIVEBLKS : 105344 UNEXPIREDBLKS : 4307412 EXPIREDBLKS : 0 TUNED_UNDORETENTION : 43402
The dba_undo_extents view currently looks like this -
READONLY_at_MIDDWHP>select count(*), status
2 from dba_undo_extents
3 group by status
4 /
COUNT(*) STATUS
---------- ---------
401 EXPIRED 22014 UNEXPIRED
Any ideas?
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 04 2006 - 10:50:02 CDT