Re: UNDO Tablespace Full

> Let me tell you that we have a Hybrid database (OLTP and DSS).
> That is the reason we use 2 undo tablespace, for OLTP 5 GB and for DSS
> 10 GB,
> Large processes are run seldomly, I mean by night or weekly. Those
> processes are supposed to be using the undo tablespace for DSS mode.

You still do not need more than one UNDO tablespace, even in a hybrid system. In fact, only one UNDO tablespace can be in use at any given time. Any other UNDO tablespaces are just sitting there hogging up space. Unlike dealing with the old rollback segments, Oracle will manage a properly sized UNDO tablespace just fine no matter the workload. I cannot image a scenario in which I would switch UNDO tablespaces on a regular basis as you are describing. Simply allocate enough space to the UNDO tablespace and stop worry about it. That's one of the benefits of AUM, a properly sized UNDO tablespace means the DBA does not have to worry about managing undo segments any more. Let Oracle handle it for you.

> I'm still wondering WHY the full segments are not being released, if
> nobody are using them??
> It has been 2 days and they are still full.

What do you mean by "full"? Are you sure that any undo information even resides in the allocated undo segment? There is a big difference between the allocation of the undo segment and the usage of that undo segment. I do not see anything in your previously posted query which indicates that the undo segment is "full".

Oracle will hold undo in the tablespace for the amount of time specified by your UNDO_RETENTION parameter. In some environments, the UNDO_RETENTION can be days, so Oracle will keep that undo information around that long if it possibly can. When you switch to a new undo tablespace, Oracle will still keep the undo in the old tablespace until UNDO_RETENTION seconds have passed.

If you are running out of space in your UNDO tablespace, then the tablespace is not large enough. The UNDO tablespace typically requires more space than the old rollback tablespaces we used to know and hate.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown