Re: UNDO: 10g-style behaviour in

From: <>
Date: Sat, 4 Apr 2009 14:20:50 -0700 (PDT)
Message-ID: <>

On Apr 3, 10:29 pm, wrote:
> Hi All,
> It is my understanding that UNDO works differently in 9.2 and 10.2.
> Size of undo datafiles is fixed, AUTOEXTEND is OFF.
> 9.2: Oracle will maintain undo blocks according to UNDO_RETENTION.
> If UNDO tablespace is large and UNDO_RETENTION * undo bloc generation
> is low then the tablespace will be almost empty.
> If UNDO tablespace is small and UNDO_RETENTION * undo block generation
> exceeds tablespace size then UNDO tablespace will run out of space.
> This will cause transactions to fail with ORA-30036 error, these
> errors will be displayed in alert.log. I observed this behaviour in
> many times.
> 10.2 Oracle will ignore UNDO_RETENTION and attempt to keep undo blocks
> as long as possible without running out of space. This means that if
> more undo blocks
> get generated the retention period will be shorter. UNDO tablespace is
> always 95 - 98% full. Again, I observed this behaviour in and
> databases.
> This is consistent with what Admin Guide says.
> However after applying Patch Set to database we are
> observing 10.2-style
> behaviour. In this database UNDO_RETENTION is set to  200,000 seconds
> (almost three days) as this is very large database and some extracts
> take very long time to run.
> UNDO tablespace is 30 GB, block size is 8K. There are some spikes in
> undo generation,
> and after such spikes we observe queries failing with ORA-1555 after
> 30 - 40 min.
> There are no 30036 errors in alert.log. Tablespace is 98-99% full.
> I wouldn't be surprised if Oracle "backported" 10.2-style behaviour in
> but it is not mentioned anywhere in the Release Notes for
> Patch Set.

I would not be especially surprised either ... can you provide some sample sql ( aka reproducible test case ) and we can run it in various environments on some of our test systems?

Have you volleyed it up to oracle support? Received on Sat Apr 04 2009 - 16:20:50 CDT

Original text of this message