RE: dropping Undo Tablespace

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <godwin.ror@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 31 May 2007 17:53:57 -0400

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Godwin vincent
Sent: Thursday, May 31, 2007 2:53 PM
To: oracle-l@xxxxxxxxxxxxx
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.

Other related posts: