Re: why undotbs01.dbf too big

From: <>
Date: Thu, 21 Aug 2008 09:20:34 -0700 (PDT)
Message-ID: <>

On 21 Aug, 16:27, wrote:
> In addition to what others had to say, it's possible you have a
> guaranteed retention. Check "retention" in dba_tablespaces for the
> undo tablespace. A high undo_retention combined with guarantee option
> and with autoextend on, will lead to high undo datafile size. Whether
> you really need a guarantee option is up to you to decide.
> -Madhu Sreeram

great tips from everyone - thanks
SQL> select RETENTION from dba_tablespaces where tablespace_name = 'UNDOTBS1'; RETENTION

Anyway I think that the cause of the large undo tbs is because of delete from tablename where date> x days, this seems to take a long time - over the 900 secs undo_retention period <sigh> - this is I know (now) a separate issue from setting up the tablespace properly with a max size.
Is is true BTW that you should enter "commit" when the delete has finished?
would it help speed up the delete by setting a day on year flag on event insertion and creating an index? Received on Thu Aug 21 2008 - 11:20:34 CDT

Original text of this message