Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Reusing UNEXPIRED UNDO blocks
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?
![]() |
![]() |