SQL rather than OraKill
Can I write SQL or PL/SQL rather than using OraKill? Thanks!
If you mean oracle , yes you can .
There are advantages using oracle over microsoft SQL Sever
There are advantages using oracle over microsoft SQL Sever
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.
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.
ASKER
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.
I use Windows Server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I use this method, will the process handle by Operating System get killed immediately or not?
It is expected that the process will be killed immediatelly.
ASKER
but mrjoltcola said "alter system kill session ..." command will not cleanup the session completely
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.
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.
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.
"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.
You can give a try openning a session and killing it with 'immediate'
Experiments will give you more confidence.
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.
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.
hmm, quote font problem
ALTER SYSTEM DISCONNECT SESSION 'sid , serial' IMMEDIATE
ALTER SYSTEM DISCONNECT SESSION 'sid , serial' IMMEDIATE
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
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_23a.htm#2053604
ASKER
So which one is better?
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION sid , serial IMMEDIATE
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION sid , serial IMMEDIATE
@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.
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.
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.
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.
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
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
alter system kill session 'sid, serial#';