Oracle-L: Re: Re:Killed status

From: shuan.tay$PCI鄭祺勛$ <shuan.tay_at_pci.co.id>
Date: Mon, 30 Dec 2002 06:09:08 -0800
Message-ID: <F001.00524257.20021230060908@fatcity.com>

All righty, guys....
thanks for the reply.

Original Message ----- To: "shuan.tay(PCI¾G¸R³Ô)" <shuan.tay_at_pci.co.id>; "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, December 30, 2002 8:46 PM

First, find the 'killed' session is v$session and match the paddr with the addr
in v$process to find the spid(system process id). I assume your running some
sort of Unix, so then run a kill -9 on that spid.

Now, take what you found in v$session and find the dumb bunny who has turned off
their client workstation before closing their Oracle session & blow their head
off.

The main reason that I've found over the last 10 years for this situation to exist is some a^&hole powers off their workstation of else kills the process on
their workstation while the database is processing some statement for them. Oracle wants to send them the message of the status of their command and session, but can't since their not listening any more. People like this will
hold locks forever, and even prevent anything other than a "shutdown abort" from
succeeding. People like this get somewhat of a different handling for me. First I kill off their session by the above means, which will wake pmon up and
cause it to rollback everything rather quickly, and their account gets locked
until their manager requests that it be re-opened. Haven't had to wield that
axe around here for some time now. Probably because it's known about and everyone does a polite shutdown. AHHH, cooperation is so nice!!

Dick Goulet

____________________Reply Separator____________________
Author: "=?big5?B?c2h1YW4udGF5XChQQ0m+R7hSs9RcKQ==?=" <shuan.tay_at_pci.co.id>
Date:       12/25/2002 7:48 PM

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.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=big5">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=MingLiu size=2>Dear all DBAs,</FONT></DIV>
<DIV><FONT face=²OcúÅé size=2></FONT> </DIV>
<DIV><FONT face=²OcúÅé size=2>Is there any way to remove the lock of table
which

status is already marked as "killed"?</FONT></DIV>
<DIV><FONT face=²OcúÅé size=2>No matter how many times i try to kill the
session,
it still marked as "killed",</FONT></DIV>
<DIV><FONT face=²OcúÅé size=2>but it still won't release the

lock.</FONT></DIV>

<DIV><FONT face=²OcúÅé size=2></FONT> </DIV>
<DIV><FONT face=²OcúÅé size=2>Thanks in advance.</FONT></DIV></BODY></HTML>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?UTF-8?Q?shuan.tay\=28PCI=E9=84=AD=E7=A5=BA=E5=8B=9B\=29?=
  INET: shuan.tay_at_pci.co.id

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_at_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).

Received on Mon Dec 30 2002 - 08:09:08 CST