RE: dropping Undo Tablespace

From: Kerber, Andrew W. <Andrew.Kerber_at_umb.com>
Date: Thu, 31 May 2007 15:28:58 -0500
Message-ID: <D40740337A3B524FA81DB598D2D7EBB307EF0268@x6009a.umb.corp.umb.com>

You could always bring the database down for maintenance.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Godwin vincent Sent: Thursday, May 31, 2007 3:10 PM
To: Ted Coyle
Cc: oracle-l_at_freelists.org
Subject: Re: dropping Undo Tablespace

Hi,

Thank you all for the quick response.

Yes, killing the transactions would be the last option and i think i will have to wait until those transactions finish and all first undo tablespace segments offline.

Riyaj - My fast_start_parallel_rollback has been set to LOW (default, i guess). And following is the result for the USED_UBLK and USED_UREC for the respective sessions.

SQL> select d.used_ublk, d.used_urec, a.usn, e.sid from v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e where a.usn=b.usn and a.name=c.segment_name and a.usn=d.xidusn and d.addr=e.taddr and b.status in ('PENDING OFFLINE','UNKNOWN');

 USED_UBLK  USED_UREC        USN        SID
----------               ----------            ----------    ----------
         1                    2                    20        22

       692            60955                   37         16
       386            29993                    4         34

What could be infered from this USED_UBLK and USED_UREC in this case?

Thanks,

Godwin.

On 5/31/07, Ted Coyle <oracle-l_at_webthere.com> wrote:

The rollback may be huge and it might take longer than waiting for it to complete. Just a thought.

Safe is relative. I don't think anyone can answer if it is safe to kill long running on your system from the app perspective, but I think it is Oracle/technically safe.

I use the same undo reduction process in production warehouses, but I've never done by killing sessions.

. 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. "

Ted

No virus found in this outgoing message. Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.4/825 - Release Date: 5/30/2007 3:03 PM

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

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

Received on Thu May 31 2007 - 15:28:58 CDT