RE: dropping Undo Tablespace

Depends on what you mean by "safe."  

If you take a look at the session stats, is it still working (consuming resources?) If it is a large transaction just awaiting some fool to commit and log off you may be in for a long wait if you kill it because you'll have to wait for it to roll back.  

If it is still grinding away, they you probably have a long rollback ahead of you unless you were "fortunate" that someone wrote really bad sql so it has taken a huge amount of time to update a small amount. (Okay, there are some cases where a lot of work is required to update a small amount, and that could also be the case.)  

If it is possible to hunt down the source (user, or batch program sponsor) of the transaction you'll be safer because you'll know the functional purpose of what you're killing. That's even better than back tracking and dumping the sql, because of course you might only see the last statement of a long series in the transaction.  

Now if by safe you mean "will it corrupt my database?" then you're probably safe, but you still might have a long wait ahead of you.  

I'm pretty sure there is no way to migrate an active transaction's undo to a different UNDO tablespace, though that might be an interesting enhancement request.  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Godwin vincent
Sent: Thursday, May 31, 2007 2:53 PM
To: oracle-l_at_freelists.org
Subject: dropping Undo Tablespace

<snipped a ton of details>

It has been almost 2 days these 3 transactions have started and still running. I wanted to drop the first UNDO tablespace ones all transactions have been completed and all segments are offline but these 3 transactions are preventing this. The only option i see here is to kill the session (22,16,34), which will offline all the undo segments and then drop the first undo tablespace. This is a production box, so just wanted to confirm with you. Is it safe to go ahead and kill the session? is there any other work around in this case for dropping the first undo tablespace.

Thanks,

Godwin.

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