Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBQA1vD26393 for ; Thu, 26 Dec 2002 04:01:57 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBQA1uc26388 for ; Thu, 26 Dec 2002 04:01:57 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id WAA94593; Wed, 25 Dec 2002 22:44:08 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00522144; Wed, 25 Dec 2002 22:18:42 -0800 Message-ID: Date: Wed, 25 Dec 2002 22:18:42 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Arup Nanda" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Arup Nanda" Subject: Re: Killed status Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C2AC7C.609C1880" ------=_NextPart_000_0010_01C2AC7C.609C1880 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable This means the transaction issued by the session is still rolling back. = The session is not killed completely or the table locks removed till the = transaction completely rolls back. So how do you know when it's going to be over? Just issue the following = query SELECT USED_UBLK FROM V$TRANSACTION WHERE ADDR =3D (SELECT TADDR FROM V$SESSION WHERE SID =3D yoursid) The USED_BLK shows number of blocks used by this transaction in the UNDO = segments and when this transaction rolls back, the blocks are released. = Issue this query repeatedly and you sould see the USED_UBLK figure = dropping and that will give you some indication how fast the roll back = is taking place. HTH Arup Nanda www.proligence.com ----- Original Message -----=20 From: shuan.tay(PCI=BEG=B8R=B3=D4)=20 To: Multiple recipients of list ORACLE-L=20 Sent: Wednesday, December 25, 2002 10:48 PM Subject: Killed status Dear all DBAs, Is there any way to remove the lock of table which status is already = marked as "killed"? No matter how many times i try to kill the session, it still marked as = "killed", but it still won't release the lock. Thanks in advance. ------=_NextPart_000_0010_01C2AC7C.609C1880 Content-Type: text/html; charset="big5" Content-Transfer-Encoding: quoted-printable
This means the transaction issued by = the session is=20 still rolling back. The session is not killed completely or the table = locks=20 removed till the transaction completely rolls back.
 
So how do you know when it's going to = be over? Just=20 issue the following query
 
SELECT USED_UBLK
FROM V$TRANSACTION
WHERE ADDR =3D (SELECT TADDR FROM = V$SESSION WHERE SID=20 =3D yoursid)
 
The USED_BLK shows number of blocks = used by this=20 transaction in the UNDO segments and when this transaction rolls = back, the=20 blocks are released. Issue this query repeatedly and you sould see = the=20 USED_UBLK figure dropping and that will give you some indication how = fast the=20 roll back is taking place.
 
HTH
 
Arup Nanda
www.proligence.com
----- Original Message -----
From:=20 shuan.tay(PCI=BEG=B8R=B3=D4) =
Sent: Wednesday, December 25, = 2002 10:48=20 PM
Subject: Killed status

Dear all DBAs,
 
Is there any way to = remove the lock of table which=20 status is already marked as "killed"?
No matter how many times = i try to kill the session,=20 it still marked as "killed",
but it still won't = release the lock.
 
Thanks in=20 advance.
------=_NextPart_000_0010_01C2AC7C.609C1880-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: arupnanda@hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).