Pythian Group

Argh! Of all the frustrating, partially-completed features Oracle has released, this is the most frustrating. Did I mention this frustrating feature is frustrating when you get bitten by it? Why? Because you only need it when you actually really, really need it.

What am I referring to? FLASHBACK QUERY on a table that lives in a database with a large UNDO_RETENTION specified with lots and lots of UNDO_TABLESPACE space.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     604800
undo_tablespace                      string      UNDOTBS_1
SQL> 

So, what do we have here? UNDO_RETENTION to set somewhere in the 7-days range. How much space do we have in the tablespace? Read the rest of this entry . . .

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!