Re: oracle 9i automatic undo management - space usgae 100%

On 10 Dec 2004 08:31:34 -0800, premmehrotra_at_hotmail.com wrote:

>I have oracle 9i database on HP UNIX 11i with automatic undo
>management. I created
>undo tablespace to be 1800M. When I look in OEM, storage, it shows undo
>tablespace as 100% used, but I do not see any errors in alert.log file
>and any other errors. Is this normal, i.e., is I cause for concern. I
>have following parameters in "init.ora"
>
>undo_management string AUTO
>undo_retention integer 604800
>undo_suppress_errors boolean FALSE
>undo_tablespace string UNDOTBS1
>
>it maybe that I have asked undo_retnetion to be too large, but my
>unerstanding is that Oracle will ingnore this parameter if there
>is not enough space anyway?
>
>
>Thanks,
>Prem
>

Exactly. But Oracle still does all that it can to keep the UNDO_RETENTION time, thus it keeps allocating new space rather than reclaiming old space which is technically free but still necessary for it to offer flashback UNDO_RETENTION seconds back in time.

If you don't experience ORA-01555 or ORA-30036, is it fine as such. But beware that UNDOTBS1 may grow enormously if the transaction load on your DB increases.

Instead, figure out how much UNDO_RETENTION you *really* need, if anything at all.

• Kenneth Koenraadt