Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: UNTO TBS behavior in 9i

Re: UNTO TBS behavior in 9i

From: w b <>
Date: Fri, 26 Jan 2007 17:31:51 +0000 (GMT)
Message-ID: <>

My understanding is that for an insert, the undo consists of the new rowid (so row can be deleted in a rollback) not the before image block so undo size should be minimal.    


"NEW" <> wrote:   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
----- Original Message -----
From: "Daniel W. Fink"
Cc: "oracle-l"
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
> 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.
> >
> >
> --


 Web email has come of age. Don't settle for less than the All New Yahoo! Mail.
Received on Fri Jan 26 2007 - 11:31:51 CST

Original text of this message