Reusing UNEXPIRED UNDO blocks
- From: Stephen Barr <ascaroth969@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 4 Apr 2006 15:24:45 +0100 (BST)
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'
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@MIDDWHP>select count(*), status
2 from dba_undo_extents
3 group by status
4 /
COUNT(*) STATUS
---------- ---------
401 EXPIRED
22014 UNEXPIRED
Any ideas?
___________________________________________________________
NEW Yahoo! Cars - sell your car and browse thousands of new and used cars
online! http://uk.cars.yahoo.com/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
