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