Reusing UNEXPIRED UNDO blocks

From: Stephen Barr <ascaroth969_at_yahoo.co.uk>
Date: Tue, 4 Apr 2006 15:24:45 +0100 (BST)
Message-ID: <20060404142445.11219.qmail@web86901.mail.ukl.yahoo.com>

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
---------- ---

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?

NEW Yahoo! Cars - sell your car and browse thousands of new and used cars online! http://uk.cars.yahoo.com/
--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 04 2006 - 09:24:45 CDT