Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Om.

Asked: November 26, 2002 - 10:22 am UTC

Last updated: September 09, 2013 - 11:04 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Q1) Generally we nake use of DBA login to kill user sessions whenever required.

Is it any way (shell script etc) which we can write and which can be used by the developers to kill there sessions.

Q2) Is there any way to get SID apart from looking at v$session
or any other V$ views in ORACLE.

Many thanks for always helping us.

Best regards,
Om

and we said...

Have someone who has been granted ALTER SYSTEM and SELECT on v_$session directly create the following script (read
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
for why "directly" is key here)

create or replace procedure kill_session( p_sid in number, p_serial# in number )
as
begin
for x in ( select *
from v$session
where username = USER
and sid = p_sid
and serial# = p_serial# )
loop
execute immediate 'alter system kill session ''' ||
p_sid || ',' || p_serial# || '''';
dbms_output.put_line( 'Alter session done' );
end loop;
end;

Now, grant execute on that to whomever. Now, anyone can kill a session owned by the same username as they are logged in as (eg: their sessions)






Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Tom Kyte
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
Tom Kyte
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?
Tom Kyte
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;
/

Tom Kyte
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.
Tom Kyte
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?
Tom Kyte
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?
Tom Kyte
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
Tom Kyte
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.
Tom Kyte
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?
Tom Kyte
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).
Tom Kyte
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.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here