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).
Daniel FinkStephen Barr <email@example.com>
When do oracle reuse an unexpired undo block?
We have a situation where there are four datafiles
associated with the UNDO tablespace -
READONLY@MIDDWHP>select file_id, autoextensible
2 from dba_data_files
3 where tablespace_name = 'UNDOTBS1'
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
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
: 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@MIDDWHP>select count(*), status
2 from dba_undo_extents
NEW Yahoo! Cars - sell your car and browse thousands of new and used cars online! http://uk.cars.yahoo.com/