Devart Blog

How To: Kill User Sessions in Oracle

Posted by on July 27th, 2010

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:

DECLARE
    PROCEDURE KILL_ALL_USER_SESSION_DBMS_SQL(P_USERNAME_SESSION IN VARCHAR2 := '')
    IS
    V_CURSOR_NAME_SESSION NUMBER := DBMS_SQL.OPEN_CURSOR;
    V_STMT_SESSION VARCHAR2(500) := 'SELECT SID AS SID, SERIAL# AS SERIAL
        FROM V$SESSION
        WHERE USERNAME IS NOT NULL AND USERNAME = '||''''||P_USERNAME_SESSION||'''';
    V_EXEC_SESSION NUMBER;
    V_SID_SESSION NUMBER;
    V_SERIAL_SESSION NUMBER;
    V_CURSOR_NAME_SESSION_KILL NUMBER;
    V_STMT_SESSION_KILL VARCHAR2(500);
    V_EXEC_SESSION_KILL NUMBER;
    BEGIN
        DBMS_SQL.PARSE(V_CURSOR_NAME_SESSION, V_STMT_SESSION, DBMS_SQL.NATIVE);
        V_EXEC_SESSION := DBMS_SQL.EXECUTE(V_CURSOR_NAME_SESSION);
        DBMS_SQL.DEFINE_COLUMN(V_CURSOR_NAME_SESSION,1,V_SID_SESSION);
        DBMS_SQL.DEFINE_COLUMN(V_CURSOR_NAME_SESSION,2,V_SERIAL_SESSION);
        LOOP
            EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR_NAME_SESSION) = 0;
            DBMS_SQL.COLUMN_VALUE(V_CURSOR_NAME_SESSION,1,V_SID_SESSION);
            DBMS_SQL.COLUMN_VALUE(V_CURSOR_NAME_SESSION,2,V_SERIAL_SESSION);
            V_STMT_SESSION_KILL := 'ALTER SYSTEM KILL SESSION '''
                ||V_SID_SESSION||','||V_SERIAL_SESSION||'''';
            V_CURSOR_NAME_SESSION_KILL := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(V_CURSOR_NAME_SESSION_KILL, V_STMT_SESSION_KILL, DBMS_SQL.NATIVE);
            V_EXEC_SESSION_KILL := DBMS_SQL.EXECUTE(V_CURSOR_NAME_SESSION_KILL);
            DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION_KILL);
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION_KILL);
        DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION);
    END;
BEGIN
    KILL_ALL_USER_SESSION_DBMS_SQL('SCOTT');
    -- specify additional calls if necessary
END;

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 Reply