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.

Answer 1

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.