FreeLists / oracle-l / Reusing UNEXPIRED UNDO blocks

[Stephen Barr <ascaroth969@xxxxxxxxxxx>]

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/
--
http://www.freelists.org/webpage/oracle-l