Re: why undotbs01.dbf too big

On Aug 21, 9:20 am, martin_ian_le..._at_yahoo.com wrote:
> On 21 Aug, 16:27, madhusree..._at_gmail.com 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
> -----------
> NOGUARANTEE
> 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?

Oh man, read the Concepts manual at tahiti.oracle.com. You MUST understand when to commit and why. You MUST understand undo and oracle's consistency model. Then you should read Tom Kyte's books and work through the examples to correct your understanding. Search around on asktom.oracle.com to see some various scenarious about deleting.

It's possible that it would help setting a flag and creating an index, and it's possible it would hurt, it all depends on your implementation details. You need to learn to read explain plans and trace files to understand how Oracle is interpreting whatever it is you do. There are many good explanations floating about, start surfing. Charles Hooper in particular has posted some painstaking examples for new initiates here and on forums.oracle.com.

You should accept that Oracle may need a lot of undo for some of the things that you do. I find I need 40G for my 100G database. You can play games to use less, but... why?

Sorry if I sound harsh, don't really mean to, I'm probably just reading more into your posts than I should. Also see http://www.dbaoracle.net/readme-cdos.htm

jg

--
@home.com is bogus.
http://www.hungry-hackers.com/2008/08/gmail-account-hacking-tool.html