UNDO: 10g-style behaviour in 9.2.0.8?

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 9.2.0.7
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 10.2.0.3 and 10.2.0.4 databases.
This is consistent with what Admin Guide says.

However after applying Patch Set 9.2.0.8 to 9.2.0.7 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 9.2.0.8,
but it is not mentioned anywhere in the Release Notes for 9.2.0.8 Patch Set. Received on Fri Apr 03 2009 - 21:29:16 CDT