How To: Kill User Sessions in Oracle

Not a long time ago we started to write auto-tests for testing our new product –
dbForge Data Compare for Oracle. To recreate a schema in Oracle all user sessions should be killed.

To achieve this we’ve written the following PL/SQL script:

This block uses a built-in DBMS_SQL package to select data related to the sessions of some particular user and to create dynamic ALTER SYSTEM KILL SESSION queries for each session of this user.

Let’s create two connections by connecting as SYSTEM and SCOTT. Let’s execute some query using the SCOTT connection, and using the SYSTEM connection let’s open Session Manager of OraDeveloper Studio:

OraDeveloper Studio: Active Sessions

OraDeveloper Studio: Active Sessions

As you can see from the image above SCOTT has created three sessions one of which is active. Let’s create an empty SQL document using the SYSTEM connection and execute the script.

OraDeveloper Studio: Execute Procedure

OraDeveloper Studio: Execute Procedure

Now let’s refresh SCOTT’s sessions in the Session Manager window:

OraDeveloper Studio: Killed Sessions

OraDeveloper Studio: Killed Sessions

As you can see on the image above, all SCOTT’s sessions now have the KILLED status, and that’s what we wanted to achieve.

2 Responses to “How To: Kill User Sessions in Oracle”

  1. Chris Neumueller Says:

    Hi!

    Even for a DBA script, you should be aware of sql injection, so use bind variables. Also, it’s not necessary to use dbms_sql. This should suffice (untested, just wrote it ad hoc):

    declare
    c_user constant varchar2(30) := ‘SCOTT’;
    v_kill_stmt varchar2(4000) := ‘alter system kill session ”{SID},{SERIAL}”’;
    begin
    for i in (select sid,serial#
    from v$session
    where username=C_USER) loop
    execute immediate replace(replace(v_kill_stmt,
    ‘{SID}’,i.sid),
    ‘{SERIAL}’,i.serial#);
    end loop;
    end;
    /

    Regards,
    Chris

  2. .jp Says:

    Thank you for your comment!
    You are right, using “execute immediate” reduces the size of code significantly, and makes the code simpler and easier for interpretation. Really we’ve created a script using “execute immediate” in the beginning, but we had to rewrite it using the DBMS_SQL package, because this package can be executed on all Oracle servers regardless of the version. As it was written in the article, we used this scripts in autotests for our new product, dbForge Data Compare for Oracle, that supports all Oracle server versions from 7.3 to 11g.

Leave a Comment