Re: Flush UNDOTBS?

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
http://www.mgogala.com

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


Other related posts: