Kill sessions
Om, November 26, 2002 - 6:58 pm UTC
Excellent.It's what I was looking for.
Daniel Liu, December 01, 2002 - 9:28 pm UTC
We have implemented similar procedure to kill long running query in Web EJB environment.(user tired of waiting,change
search criteria,launch new query,the previous query get killed.)
Since binding is not allowed in DDL,will thousands statement
like this trash the shared pool?
December 02, 2002 - 7:08 am UTC
If you have to do this thousands of times, you have what I would characterize as a "bug" in your application.
I would be looking at the cause here -- why do you beans (which should be short, sweet tiny transactions) take so long that the user gets "bored"?
Yes, just as thousands of unique inserts could, thousands of anything could be bad for you.
A reader, November 07, 2006 - 12:37 pm UTC
How to Kill sessions remotely
A reader, July 06, 2007 - 3:32 pm UTC
Hello Tom,
How can we kill a session remotely ?
We have a central monitoring DB which monitors all production DBs which are physically distinct.
How can I kill a paticular session on a particular DB from my host monitoring DB.
Alter system kill session is a command line which doesnot uses DB link.
I dont want to have a procedure on allthe Target DB's and call that thru a DB link.
I tried dbms_utility.exec_ddl using dblink that would create objects on the remote DB but wont kill session ?
I looked up dbms_session , dbms_system ,dbms_utility ,dbms_support none have a Kill session proc
Any suggestions
July 07, 2007 - 10:44 am UTC
you have to connect to the remote database, plain and simple.
you could use dbms_job/dbms_scheduler as well, run dbms_job at the remote site and schedule:
begin execute immediate 'alter system kill session ''' || sid||','||serial# || ''''; end;
after you commit, the job will run (if jobs are configured) as a side effect.
RAC
marc, July 07, 2007 - 9:12 pm UTC
Is there anyway to write "kill_session" on a rac cluster to go through each node and kill the user?
July 08, 2007 - 9:25 am UTC
you could submit a job using dbms_job on each node (using the instance parameter).
Running with that notion...
Philip Moore, July 25, 2007 - 4:55 pm UTC
Hey Tom,
I saw your recommendation for this - and I ran with it. I believe this code will do what you propose. Hopefully it will help other RAC users out there... Please offer your advice on if it is "evil" or not...
Thanks!
DROP TABLE dbms_job_errors CASCADE CONSTRAINTS;
CREATE TABLE dbms_job_errors (job_number INTEGER NOT NULL
, failure_date DATE DEFAULT SYSDATE NOT NULL
, username VARCHAR2(30) DEFAULT USER
, error_message VARCHAR2(4000)
, CONSTRAINT dbms_job_errors_pk PRIMARY KEY (job_number, failure_date)
)
TABLESPACE users
LOGGING
MONITORING;
CREATE OR REPLACE PUBLIC SYNONYM dbms_job_errors FOR dbms_job_errors;
GRANT SELECT ON dbms_job_errors TO PUBLIC;
/* --------------------------------------------------------------------------- */
CREATE OR REPLACE PROCEDURE SYS.rac_kill_session
(
p_instance_number IN gv$instance.instance_number%TYPE
, p_sid IN v$session.SID%TYPE
, p_serial IN v$session.serial#%TYPE
)
AUTHID DEFINER AS
-- Variables
l_session_exists PLS_INTEGER;
l_instance_name gv$instance.instance_name%TYPE;
l_this_instance_number v$instance.instance_number%TYPE;
l_this_instance_name v$instance.instance_name%TYPE;
l_job dba_jobs.job%TYPE;
-- Custom Exceptions
session_does_not_exist EXCEPTION;
instance_does_not_exist EXCEPTION;
BEGIN
-- First see if the session really exists...
BEGIN
SELECT 1
INTO l_session_exists
FROM gv$session
WHERE inst_id = p_instance_number
AND SID = p_sid
AND serial# = p_serial;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE session_does_not_exist;
END;
SELECT instance_number
, instance_name
INTO l_this_instance_number
, l_this_instance_name
FROM v$instance;
/* If they are trying to kill a session on this instance
just issue the ALTER SYSTEM KILL SESSION command... */
IF p_instance_number = l_this_instance_number THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
DBMS_OUTPUT.put_line ('Successfully Killed the session with SID: ' || p_sid || ', Serial#: ' || p_serial || ' on RAC Instance #: ' || p_instance_number || ' (RAC Node: ' || l_this_instance_name || ').');
ELSE
/* We must use DBMS_JOB with the instance parameter in order to kill this session
since it is on a different RAC node... */
-- Get the instance node name
BEGIN
SELECT instance_name
INTO l_instance_name
FROM gv$instance
WHERE instance_number = p_instance_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE instance_does_not_exist;
END;
DBMS_JOB.submit (job => l_job
, what => 'DECLARE l_error VARCHAR2(4000); '
|| 'BEGIN '
|| ' EXECUTE IMMEDIATE ''ALTER SYSTEM KILL SESSION '''''
|| p_sid
|| ','
|| p_serial
|| '''''''; '
|| 'EXCEPTION '
|| ' WHEN OTHERS THEN '
|| ' l_error := SQLERRM; '
|| ' INSERT INTO dbms_job_errors (job_number, failure_date, username, error_message) '
|| ' VALUES (sys_context(''USERENV'', ''BG_JOB_ID''), SYSDATE, USER, l_error); '
|| ' COMMIT; '
|| 'END;'
, INSTANCE => p_instance_number
, FORCE => FALSE
);
-- Commit to launch the job
COMMIT;
DBMS_OUTPUT.put_line ('Launched DBMS_JOB #: ' || l_job || ' to kill the session with SID: ' || p_sid || ', Serial#: ' || p_serial || ' on RAC Instance #: ' || p_instance_number || ' (RAC Node: ' || l_instance_name || ').');
END IF;
EXCEPTION
WHEN session_does_not_exist THEN
raise_application_error (-20101, 'A session with SID: ' || p_sid || ', Serial #: ' || p_serial || ', on Instance #: ' || p_instance_number || ' does not exist in GV$SESSION.');
WHEN instance_does_not_exist THEN
raise_application_error (-20102, 'A RAC Node with INSTANCE_NUMBER: ' || p_instance_number || ' does not exist in GV$INSTANCE.');
END rac_kill_session;
/
July 26, 2007 - 9:32 pm UTC
except I would not create it in sys....
i would not use a public synonym....
other than that - it looks about right - did not 'test it', but the concept is correct.
Killing session on remote node - RAC 11g?
A reader, December 27, 2007 - 11:39 am UTC
If I look here:
http://download.oracle.com/docs/cd/B28359_01/rac.111/b28254/admin.htm#CACIAJHB and click "Terminating Sessions On a Specific Cluster Instance"
I see that in 11G, we may be able to use 'alter system kill session 'sid,serial#,@inst_id' immediate' to kill a session on another node. I don't have 11g, so I can't try. I'm posting as an FYI because this isn't mentioned in any reply.
December 27, 2007 - 1:26 pm UTC
thanks! another new 11gR1 feature, appreciate the followup.
sys%ORA11GR1> select inst_id, sid, serial#, username from gv$session where username is not null;
INST_ID SID SERIAL# USERNAME
---------- ---------- ---------- --------------------
1 126 3565 OPS$TKYTE
1 144 6999 SYS
2 rows selected.
sys%ORA11GR1> alter system kill session '126,3565,@1';
System altered.
Uniqueness of SID
Laxman, February 11, 2011 - 2:31 am UTC
Hi Tom,
Can you please let me know if sid in v$session is unique for a particular database instance. i.e. will there be any occurences of one or more sid's getting replicated.
If they are unique, then why do we need to give serial# while killing user session?
February 14, 2011 - 7:10 am UTC
sids repeat all of the time. sid+serial# does not.
as long as the session exists - their sid will be unique in v$session - but over time, it is not unique.
I guess they did that (sid+serial#) in the event that whilst you were typing the alter system kill command - the session you wanted to kill had logged off and a new session came into being with the same sid. You'd kill the wrong session.
What about RAC sid+serial#
Jed Walker, August 11, 2011 - 4:48 pm UTC
You say that sid+serial# will not be duplicated, but don't reference RAC. In a RAC cluster, could it be duplicated on two different instances with the inst_id being required to differentiate?
August 14, 2011 - 8:05 pm UTC
the primary keys of v$ views are different than the gv$ views.
When you query a gv$ view - you need to add instance (inst_id) to the key.
How to kill sessions from diff Nodes
Dileep, March 14, 2012 - 9:20 am UTC
Hi Tom,
i would like to kill the long running sessions from different nodes.for that i wrote a procedure in that previously i used the query like SELECT SID, serial#, inst_id
FROM gv$session
WHERE SID IN (
SELECT session_id
FROM gv$locked_object
WHERE object_id IN (
SELECT object_id
FROM dba_objects
WHERE owner = 'APPS'
AND object_name = 'ObjectName'
AND object_type = 'TABLE'));
and now i would like to change this to
SELECT t.SID, t.serial#, t.inst_id
FROM gv$session t, gv$process p
WHERE p.addr = t.saddr
AND p.inst_id = t.inst_id
AND t.SID IN (
SELECT session_id
FROM gv$locked_object
WHERE object_id IN (
SELECT object_id
FROM dba_objects
WHERE owner = 'APPS'
AND object_name = 'Object_Name'
AND object_type = 'TABLE'));
which query is the best for killing the sessions from different nodes. and what is the use of gv$process view.please help me on this.
thanks in advance
March 14, 2012 - 3:22 pm UTC
i would like to kill the long running sessions from different nodes.
why wouldn't you rather fix the underlying cause - a cause I bet I know...
I'll bet you are using a connection pool
I'll bet you sometimes see locked rows in some table that is modified by connections in that pool
I'll bet that the session associated with this lock has been idle for a long time.
I'll bet you have OPEN_CURSORS set really high (because the developers complain about running out)
I'll bet you have PROCESSES set really high and really big connection pools (because the developers complain about running out)
I'll bet your developers have some really bad 'error handling'. They've grabbed a connection, they've executed some SQL, they hit an error and fly over the close of the statement they were executing and sometimes even the release of the connection back to the pool. they are leaking cursors and connections left and right. This is a serious data integrity issue, and a hugely bad bug in their code.
and you are trying (in vain) to put a bandage on it. Stop - get the root cause fixed.
I don't know how to help you on what you asked. You said "I used to use query X, now I want to use query Y". Ok, go ahead, use query Y???
I don't know what you want here.
but i strongly suggest getting the code fixed - I know what is happening, I've seen it perhaps a million times by now.
Good Job on the rac_kill_session procedure
A reader, September 21, 2012 - 1:40 pm UTC
I made the changes (Schema, public synonym) etc and was able
to use it. I had some DDL lock issues for which I used GV$SESSION and GV$ACCESS view to build my exec statements.
Thanks for sharing
Dave, August 14, 2013 - 8:25 pm UTC
When exactly does the session usually get killed? I read this on another site:
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
So how long typically does a session stay in this 'pseudo-limbo marked for kill' state? I have seen end users eat up nearly 40 GB of temp space running 24+ hr queries. Doesn't do much good if I have to wait for the offending query to finish.
August 14, 2013 - 9:26 pm UTC
the kill session rolls back any outstanding work performed by that session, releases all resources (locks and so on) and persists as a minimal session until the client tries to do something - at which point the client will receive an ora-28 "your session has been killed" and then the session gets cleared out of v$session.
so the session is technically "dead" as soon as it is marked for kill - any resources it held are released. We are just waiting for the client to get the message (otherwise they would get an ora-3113/3114 or something equally nasty which looks like a 'bug' to an application).
if you want to have that "buggy looking" behavior - use disconnect session, if you do that - the next time the client tries to do something - they'll see something like:
ops$tkyte%ORA11GR2> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 7927
Session ID: 189 Serial number: 215
ERROR:
ORA-03114: not connected to ORACLE
session
A reader, August 28, 2013 - 4:29 pm UTC
Tom:
If you use a browser for web application and you post a bad SQL command that updates 5 million records instead of a few records and you kill the browser, does it kill the session in the database.
I noticed the SQL keeps running and hangs all other sessions.
just curious, does oracle keep running the SQL that updates 5 million records and what happens after that? Does it commit or rollback or kill session?
September 04, 2013 - 5:29 pm UTC
No, it would not typically kill the session in the database, the session in the database doesn't know until it goes to tell the application server "hey, we are done"
it is not that oracle keeps running it, it is that is keeps running until someone, anyone, knows there is a problem. Your application server (your browser is talking to an app server, not to the database) doesn't even know you closed your browswer yet - it would have to write back to you to figure out "hey, no one is home".
What you might want to do is use a time out mechanism in your middle tier so that if something takes more than N seconds - the app server would kill that thread of execution - make it have an exception so it can stop whatever it was doing.
session
A reader, September 04, 2013 - 11:18 pm UTC
Tom:
WE dont have application server. WE have oracle web server and mod_plsql.
Does mod_plsql tell the database the user closed the browser so stop the session?
I think most apache web server have the timeout feature built in by default but apache does not talk to the database directly (mod_plsql does).
September 09, 2013 - 11:04 am UTC
Sam,
guess what a web server running mod_plsql is...
is it an application server!!!!
you have database resource profiles. you can put a hard limit per call based on amount of cpu. something you certainly already knew as I believe we've talked about resource profiles in the past.
fully documented in the admin guide, trivial to implement. if that is what you want.