• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1591
  • Last Modified:

SQL rather than OraKill

Can I write SQL or PL/SQL rather than using OraKill? Thanks!
0
mawingho
Asked:
mawingho
  • 4
  • 4
  • 4
  • +3
1 Solution
 
sujith80Commented:
are you looking to kill a session?

alter system kill session 'sid, serial#';
0
 
SyferBCommented:
If you mean oracle , yes you can .
There are advantages using oracle over microsoft SQL Sever
0
 
mrjoltcolaCommented:
Not always.

From time to time a DBA finds that the "alter system kill session ..." command will not cleanup the session completely. Whether it be a bug, or whatever reason, historically it happens. So on UNIX we can kill the shadow process, but on Windows you must use orakill.

orakill is a last resort, however, and only to be used if:

1) alter system kill ... did not work or you need to clear locks _immediately_
2) You cannot login to the instance, or login is hanging due to system spiralling death (swapping / CPU overburden)

So orakill does have its uses and is not interchangeable with the SQL command.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
mawinghoAuthor Commented:
I use SQL to find the spid, are there anyway to directly pass those spid to orakill then kill the process. Any script or command can do?
I use Windows Server.
0
 
schwertnerCommented:
It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux.

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

First identify the offending session as follows:
SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

       SID    SERIAL# OSUSER                         PROGRAM
---------- ---------- ------------------------------ ---------------
         1          1 SYSTEM                         ORACLE.EXE
         2          1 SYSTEM                         ORACLE.EXE
         3          1 SYSTEM                         ORACLE.EXE
         4          1 SYSTEM                         ORACLE.EXE
         5          1 SYSTEM                         ORACLE.EXE
         6          1 SYSTEM                         ORACLE.EXE
        20         60 SYSTEM                         DBSNMP.EXE
        43      11215 USER1                          SQLPLUSW.EXE
        33       5337 USER2                          SQLPLUSW.EXE
The SID and SERIAL# values of the relevant session can then be substituted into the following statement:
0
 
mawinghoAuthor Commented:
If I use this method, will the process handle by Operating System get killed immediately or not?
0
 
schwertnerCommented:
It is expected that the process will be killed immediatelly.
0
 
mawinghoAuthor Commented:
but mrjoltcola said "alter system kill session ..." command will not cleanup the session completely
0
 
mrjoltcolaCommented:
Well, without the IMMEDIATE it may not.

I am speaking from past years of experience where I have seen Oracle processes hang, regardless of what you do, and kill -9 is the only way to rid. I have even seen where kill would also not work, in a rare case or two, believe it or not.

Things happen that should not, and have to be dealt with. For example, you won't find anywhere in the Oracle docs how to deal with removing shared memory segments that wont remove. This is also, possibly a kernel bug, but I have run into it. The only solution is to reboot the box.

So schwertner is theoretically right, but sometimes theory is not 100% implemented in practice, as he will also agree with I'm sure. I was also speaking from old experience, so it is possible that Oracle no longer recommends using OraKill, I do not know for sure.
0
 
mrjoltcolaCommented:
Also, do not let my paranoia dissuade you, try using ALTER SESSION as first resort always. Even when using OraKill, ALTER SESSION has always been recommended first.
0
 
schwertnerCommented:
"alter system kill session ..."  without 'immediate' will mark the session as snipped and wait the application to do an action and only then the application will be informed that the session is killed, sniped or whatever and the session will  be deleted.

You can give a try openning a session and killing it with 'immediate'
Experiments will give you more confidence.
0
 
sdstuberCommented:
rather than "kill session",  if you want instant cleanup you probably want "disconnect session"  
a "kill" even with immediate forces a rollback which can take a long time.


ALTER SYSTEM DISCONNECT SESSION  sid , serial  IMMEDIATE

from the 10g SQL Reference

Use the DISCONNECT SESSION clause to disconnect the current session by destroying
the dedicated server process (or virtual circuit if the connection was made by way of a
Shared Sever). ...
The IMMEDIATE setting disconnects the session and recovers the entire session
state immediately, without waiting for ongoing transactions to complete


The KILL SESSION clause lets you mark a session as terminated, roll back ongoing
transactions, release all session locks, and partially recover session resources. ...
Specify IMMEDIATE to instruct Oracle Database to roll back ongoing
transactions, release all session locks, recover the entire session state, and return
control to you immediately.
0
 
sdstuberCommented:
hmm, quote font problem

ALTER SYSTEM DISCONNECT SESSION 'sid , serial' IMMEDIATE
0
 
sdstuberCommented:
oops, just noticed the 9.x zone.  that's ok,  disconnect session applied to 9i as well as 10g

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_23a.htm#2053604
0
 
mawinghoAuthor Commented:
So which one is better?
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION  sid , serial  IMMEDIATE
0
 
mrjoltcolaCommented:
@ststuber: >>a "kill" even with immediate forces a rollback which can take a long time.

Both force rollback. The rollback has to happen, one way or another, to guarantee database consistency. The question is whether you want Oracle to kill the process and return control fast or not. DISCONNECT IMMEDIATE is probably preferred in this case, because it does indeed nuke the server process, whereas KILL IMMEDIATE will leave the server process around for reuse, but will still free locks and rollback. You can verify this by testing in V$SESSION, the SID will be recycled when using KILL but not DISCONNECT. I feel that DISCONNECT IMMEDIATE more closely models the OS level kill, but probably is not good in a shared server environment. Since I don't use DISCONNECT, I have not tested with a shared server, and now that I have mentioned it, I must satisfy my curiosity by testing it. I'll post back when I do.

@mawingho: >> So which one is better?

IF you want the session gone immediately, either KILL or DISCONNECT will work, theoretically. It is when you see a process hang that the question exists, but it is not supposed to happen. I have not seen it with 10g, so I cannot say. If it does, I kill the OS process and don't worry about it. Again, this is an old-time bug that DBAs have seen since Oracle 7/8 days, and unless I can test it with newer versions, I cannot have an opinion on it. Obviously the problem existed, or Oracle would never have provided orakill, but nowadays it is possibly more for backwards compatibility than anything.

The only value of using DISCONNECT, to me, is that it allows you to use POST_TRANSACTION, which is the way to schedule a "kill" nicely, basically tell Oracle to kill the session _after_ the transaction is complete. I've never had a use for it, but it is nice to have. Its similar to "SHUTDOWN NORMAL", noone uses it because when its time to shut off the lights, you cannot risk an idle session holding up the show due to an uncommitted transaction.


** The Short Version **

Use IMMEDIATE with either command, and don't worry about OraKill.exe until you need it. You will know, because the process will not respond to either command, you'll have a lock, and the last resort will be kill -9 or orakill. Until you need it, don't worry about it.
0
 
schwertnerCommented:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
is the most common way.
Rollback and all activities of PMON are unavoidable and out of discussion.
To compare both methods I will suggest to make experiments.
They will show you whta happens and how fast.
0
 
sdstuberCommented:
to be honest,  I can never tell "externally" the difference between doing a disconnect vs a kill.    But disconnect with immediate "should" be faster, at least as I read the docs, but I've been wrong before.  :)

and yes,  as mrjoltcola stated above, the rollback must occur regardless of the method and regardless of immediate or not.  I'm sorry if snippets from the documentation implied otherwise and caused confusion
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now