Re: dropping Undo Tablespace

  • From: "Godwin vincent" <godwin.ror@xxxxxxxxx>
  • To: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
  • Date: Thu, 31 May 2007 17:42:03 -0400

Hi,

Riyaj - Following is the output i got after i executed the query,

SQL> select sid, last_et_call, status from v$session where sid in
(122,91,42)

SID       LAST_CALL_ET     STATUS
------ ------------------------------ ------------------
22           208832               ACTIVE
16             384680               ACTIVE
34             385305               ACTIVE

I dont understand the significance of this value. If it indicates how
recently the query was active, then the SID has the pending query from about
2 days. Any insight?

Thanks,
Godwin.
On 5/31/07, Shamsudeen, Riyaj <RS2273@xxxxxxx> wrote:

  Godwin

            These pending transactions are very small. 1000 block rollback
shouldn't take too long, shouldn't cause major issues either. Just find out
if the sessions are active or are they executed DML in the past and those
changes are uncommitted. V$session.last_call_et and state column should
help.

            If I were you, I would kill these sessions [ if they are not
active ], as these transactions are pending for couple of hours anyway.
 ------------------------------

*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Godwin vincent
*Sent:* Thursday, May 31, 2007 3:10 PM
*To:* Ted Coyle
*Cc:* oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxxxxx> 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



Other related posts: