Re: UNTO TBS behavior in 9i

Hi all,

I think the retention setting is a red herring here. I *think* its a real basic issue. Someone posted the suggestion to use the append hint. I reckon this should work. You need to remember that for an insert the UNDO is a copy of the before image and if your inserting into 30G of blocks that have *some space* free, then all of these blocks need to be saved as UNDO.

Maybe your PCTFREE settings need looking at. Has a lot of data been deleted - this would leave a lot of free blocks that will end up in the UNDO. Perhaps you can rebuild the table before the insert ...or even truncate it?

Just ideas, hope they help
Cheers
Tony
----- Original Message -----
From: "Daniel W. Fink" <daniel.fink_at_optimaldba.com> To: <veeeraman_at_gmail.com>
Cc: "oracle-l" <oracle-l_at_freelists.org>
Sent: Wednesday, January 24, 2007 9:23 PM Subject: Re: UNTO TBS behavior in 9i

> Are there triggers on the table that cause insert/update/delete on other
> tables? This would generate undo as well.
>
> There might be other activity in the database that generates undo
> (statspack comes to mind). You will need to track the info from
v$transation
>
> Ram Raman wrote:
> > Thanks for your replies.
> >
> > 1. We are not doing multiple commits. Just one INSERT INTO SELECT
> > statement with one commit at the end. There were no
> > other transactions when we started this one.
> >
> > 2. The space for both data and index is about 5.5Gb.
> >
> > Last time we ran the process, the 30GB UNDO TBS was close to 100% free
> > when we started the process and the UNDO TBS became 100% full and then
> > erred out because of lack of space. Why would the undo TBS grow out
> > to be almost 30GB causing the process to fail esp. when no other
> > process is running in the database. The only other active processes
> > are Oracle background processes. Oracle is 9206.
> >
> > We do have the option of using Direct path insert and turning the
> > logging off, but this behavior is perplexing.
> >
> >
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l