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

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

Yesterday we have tested our application. There are lots of issues in application side and also database side. Large numbers of sessions are opened while accessing the database through front end. When ever we have exit the application, sessions are
not closed (Database sessions are hanging).Today night we will plan to test the same application.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

1.Created 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 ;

2. Privileges assigned execute the kill_your_session procedure to TEST schema.

GRANT EXECUTE ON kill_your_session to TEST;

3. Login as TEST schema.

Created 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;

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

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

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.