Last week I have faced one interested issue in my development database.

Our Application team tested one module in our database. They raised a database support request to our DBA team. Refer to the issue details.

Yesterday we have tested our application. There are numerous issues on the application side and also the database side. Large numbers of sessions are opened while accessing the database through the front end. Whenever we have to exit the application, sessions are not closed (Database sessions are hanging). Tonight we will plan to test the same application. The SCHEMA NAME is TEST. So we will need to kill the session privileges.

I am seeking any privilege is there, to kill the particular sessions. No direct session kill privileges are not available in oracle. I have found out the one article related to this same issue. They handled Created one procedure used to kill the session and grant execute on privileges to the users.

Steps

Step 1:

Create the procedure in Other schema (DBA LOGIN SCHEMA’S)

PROCEDURE NAME : kill_your_session

Create or replace procedure kill_your_session (in_sid in sys.v_$session.sid%type,
in_serial# in sys.v_$session.serial#%type)
as
row_count pls_integer ;
begin
select count (*)
into row_count
from v$session
where username = 'TEST' and sid = in_sid and serial# = in_serial# ;
if row_count > 0
then
execute immediate 'alter system kill session ''' ||
to_char (in_sid) || ', ' || to_char (in_serial#) || '''' ;
end if ;
end ;

Note:

Why am I not created the procedure in TEST schema?

The developer will change the code (like schema name change), its affect the another schema in database. So i have created the procedure in another schema.

Step 2:

Privileges assigned to execute the kill_your_session procedure to TEST schema.

GRANT EXECUTE ON kill_your_session to TEST;

Step 3:

Login as TEST schema.

Create the procedure in TEST schema

PROCEDURE NAME : pro_killsession

create or replace procedure pro_killsession(sid in number, serialnumber in number) as
begin
DBA.kill_your_session(in_sid => sid,in_serial# => serialnumber);
end;

Step 4:

Execute the below query to get SID, SERIAL# belongs to the user.

Select sid, serial#, status from v$session where username='TEST';

Step 5:

Execute the pro_killsession procedure & pass the arguments (Sid, Serial#)

I hope this article helped you to understand a kill the session privileges. Suggestions are welcome.