Oracle-L: Re:Killed status

From: <dgoulet_at_vicr.com>
Date: Mon, 30 Dec 2002 05:48:57 -0800
Message-ID: <F001.00524203.20021230054857@fatcity.com>

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: 
  INET: dgoulet_at_vicr.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_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 - 07:48:57 CST