CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11g
Jonas Sourlier
Jonas Sourlier

Reputation: 14435

Stored procedure to kill Oracle sessions by user name

This has been asked several times on the web, but none of the answers I found on Google could solve my problem.

I'd like to create a stored procedure that kills Oracle sessions. The only parameter the procedure accepts is the user name of the owner of the sessions to kill.

This is my attempt:

CREATE OR REPLACE PROCEDURE kill_user_session (
    username   IN NVARCHAR2
)
AS
    stmt varchar(5000);
    CURSOR get_sessions
        IS
        SELECT s.sid sid, s.serial# ser
            FROM v$session s, v$process p
        WHERE s.username = username
        AND p.addr(+) = s.paddr;
    session_rec get_sessions%ROWTYPE;
BEGIN
    FOR session_rec in get_sessions LOOP
        BEGIN
            stmt := 'ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' ||  session_rec.ser || '''';
            EXECUTE IMMEDIATE stmt;
        --EXCEPTION WHEN others THEN
        --    dbms_output.put_line('Error killing session: ' || stmt);
        --    dbms_output.put_line(SQLERRM);
        END;
    END LOOP;
END;
/

If I execute it like this

     exec kill_user_session('myuser');

I get an error:

ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "SYSTEM.KILL_USER_SESSION", line 17
ORA-06512: at line 1

If I change line 17 to 

            stmt := 'ALTER SYSTEM KILL SESSION "' || session_rec.sid || ',' ||  session_rec.ser || '"';

then I get

ERROR at line 1:
ORA-00026: missing or invalid session ID
ORA-06512: at "SYSTEM.KILL_USER_SESSION", line 17
ORA-06512: at line 1

I have granted the following rights to SYSTEM:

GRANT SELECT ON v$session TO SYSTEM;
GRANT ALTER SYSTEM TO SYSTEM;

But that didn't help.

EDIT: I added a dbms_output.putline to print out the stmt variable before executing it. Here's an example:

ALTER SYSTEM KILL SESSION "34,91"

If I execute this statement outside of the stored procedure, it runs fine and the session is killed. But not from inside.

Upvotes: 1

Views: 13642

Answers (2)

Ahmed
Ahmed

Reputation: 1

You can use this procedure:

{
    CREATE PROCEDURE kill_user_session (users IN VARCHAR2)
    AS
       stmt          VARCHAR (5000);
    
       CURSOR get_sessions
       IS
          SELECT s.sid sid, s.serial# ser
            FROM v$session s, v$process p
           WHERE s.username = users AND p.addr(+) = s.paddr;
    
    BEGIN
       FOR session_rec IN get_sessions
       LOOP
          BEGIN
             stmt :=  'ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' || session_rec.ser || '''' || ' IMMEDIATE';
             --dbms_output.put_line(stmt);
             BEGIN
                EXECUTE IMMEDIATE stmt;
                --EXCEPTION WHEN others THEN
          --    dbms_output.put_line('Error killing session: ' || stmt);
          --    dbms_output.put_line(SQLERRM);
             EXCEPTION
                WHEN OTHERS
                THEN
                   -- You probably need to log this error properly here.
                   -- I will just re-raise it.
                   CONTINUE;
             END;
          END;
       END LOOP;
    END;
    /
}

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

First off, you shouldn't have a semicolon in the SQL statement you pass to EXECUTE IMMEDIATE. That would cause the ORA-00911 error.

Second, it is always helpful to print out the dynamic SQL statement that you've built before you execute it. The extra semicolon may be the only error. Or there may be other errors. Those errors will inevitably be easier to debug if you can see the SQL statement that you've built (and execute it separately) rather than just looking at the code that builds the statement.

Upvotes: 3

Related Questions