Kill Session Privileges To Users
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.
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)
row_count pls_integer ;
select count (*)
where username = 'TEST' and sid = in_sid and serial# = in_serial# ;
if row_count > 0
execute immediate 'alter system kill session ''' ||
to_char (in_sid) || ', ' || to_char (in_serial#) || '''' ;
end if ;
Why I am 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.
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
DBA.kill_your_session(in_sid => sid,in_serial# => serialnumber);
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.