How often do you read the reference guide for something you already know? When I find little things like the one to follow, I wonder why Oracle doesn’t broadcast these small improvements more clearly.
I needed to change the undo_retention parameter in a 10gR2 database. Unfortunately I forgot whether the parameter was in seconds or minutes, and I wanted to see what the default was. So I open up the docs, go into the Reference guide and get the info I need from the top few lines.
I wanted to set undo_retention to some large value in order to use flashback. So I decided to read if they were any notes about setting this too large. I started reading the notes. I’ve read this in the past, so didn’t expect anything new. Then I read the following:
“For fixed-size undo tablespaces, the system automatically tunes for the maximum possible undo”
What? Fixed size undo vs autoextend undo ? That’s new!
So I clicked the link to the administrator guide and kept reading.
Apparently, when your undo tablespace is NOT autoextensible, then Oracle will switch from time based undo retention, to size based retention. Same as the good old days, where you set X amount of space for rollback segments and if there were more changes in the database … well simply you could not use flashback that far into the past. As opposed to your undo tablespace growing to something you didn’t want.
I checked back, and this feature doesn’t seem to be in 10.1, so it’s brand new. According to the notes however, it’s still time based, but based on the amount of undo you are generating and the fixed size of your undo tablespace.
Stay tuned: I will be testing this “new” behaviour sometime in the near future!
Although I was aware of this autotune feature .. however, after reading your comments I paid a visit to Metalink to read more.
If you are researching this, you might be interested in reading the juicy details in Bug 5387030 : AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS