Re: Flush UNDOTBS?

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: "J. Dex" <cemail_219@xxxxxxxxxxx>
  • Date: Tue, 07 Feb 2006 08:44:04 -0500

On 02/07/2006 07:53:21 AM, J. Dex wrote:
> I was trying to run an import.  The import failed as it runs out of UNDO.  
> After realizing that the process had died, I wanted to start another import 
> and noticed that UNDO was still 50% used.  I have already added an enormous 
> amount of space.   My import is running with commit=y and a reasonable size 
> buffer.

Something has to be using that UNDO space. On oracle 9i and above, you can use
the following query to determine what exactly is using your UNDO tablespace:

select s.username,s.sid,t.status,t.used_ublk
from v$session s,v$transaction t
where s.saddr=t.ses_addr 
order by t.status,t.used_ublk desc

What you are really interested in are only the cases where STATUS='ACTIVE'
because those blocks can not be reused.

Mladen Gogala


Other related posts: