How to kill own Oracle SQL sessions without DBA privileges?

Question

Is there a way for a user to terminate one's own session/connections, given an Oracle SID, without DBA rights?

Specifically, I can run this in my DB without admin rights:

SELECT SID, "SERIAL#", STATUS, USERNAME
 FROM V$SESSION
 WHERE 
 (USERNAME = 'LastF') 
 AND
 (STATUS = 'INACTIVE');

But when I go to kill my orphaned session (from another session to which I still have access),

ALTER SYSTEM KILL SESSION "12, 123"

I get the following:

JDBC ERROR: ORA-01031: insufficient privileges

Note: I am connecting with JDBC through R/Rstudio using the RJDBC package.

Motivation:

It doesn't appear too difficult to kill sessions in Oracle SQL: https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc008.htm#ADMIN11192
How can I kill all sessions connecting to my oracle database?

However, for non-DBA users who have orphaned connections (i.e. internet outage, 3rd party client that manages connections errors out, etc), it can be really frustrating to get:

ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit

and have to wait for timeout.

Good article about doing this here http://dba-oracle.com/t_granting_alter_system_kill_session.htm but in a nutshell either get DBA's to write a procedure that will kill sessions (with appropriate safegaurds) and grant execute on that procedure OR get them to grant ALTER SESSION (I have this on all dev databases, will certainly NOT be granted on PROD) — Shaun Peterson, Mar 20 '20 at 00:20
@ShaunPeterson - I assume you meant ALTER **SYSTEM**? ALTER *SESSION* has nothing to do with killing sessions. — , Mar 20 '20 at 03:18

score 7 · Accepted Answer · answered Mar 20 '20 at 00:27

To successfully run an ALTER SYSTEM command, you don't need to be the DBA, but you do need the ALTER SYSTEM privilege to be granted to you (or to the "user" owning the application through which you connect to the database - which may be different from "you" as the "user" of RStudio).

You have a few options:

ask the DBA to kill the session
ask to be granted the ALTER SYSTEM privilege (which is a very poor practice)
have a "supervisor" (however defined - responsible specifically for these situations) be granted the ALTER SYSTEM privilege, who will be in charge of killing such sessions
(perhaps the best option) create a packaged procedure whose only task is to kill orphaned sessions. Grant ALTER SYSTEM to the package owner, and grant execute privilege on that package to individual users (as needed). The procedure should be written to only kill specific kinds of sessions.

score 0 · Answer 2 · answered Mar 21 '20 at 13:26

You can use the below to cancel whatever is running in the session

    DECLARE
      l_status  v$session.status%TYPE;
    BEGIN

      dbms_system.set_ev( &sid, &serial, 10237, 1, '');

      LOOP
        SELECT status INTO l_status FROM v$session
          WHERE sid = &sid and serial# = &serial;
        EXIT WHEN l_status='INACTIVE';
      END LOOP;

      dbms_system.set_ev( &sid, &serial, 10237, 0, '');
    END;

score 0 · Answer 3 · answered Aug 03 '21 at 07:08

you will have to create a direct select grant on sys.v_$session

grant select on v_$session to

Where is the schema that owns the above procedure. This has to be a direct grant and not through a role.

Check the link for more details and given by Donald Burleson

score 0 · Answer 4 · edited Aug 03 '21 at 22:51

we can kill the oracle session with pid , if you are unable to identify the operating system process identifier (spid) from the query , you can issue the following query to help identify the correct session:

SELECT s.sid, s.serial#, p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.username = '<username>';

At the operating system prompt, issue the kill command and supply the operating system process identifier (spid):

kill <spid>

How to kill own Oracle SQL sessions without DBA privileges?

Motivation:

4 Answers4