KILLING MY SESSIONS IN ORACLE Author JP Vijaykumar Date Nov 14th 2009 Many a time, developers ask for procedures to kill there own sessions in the oracle db. Many of our developers are scattered around the globe in different time zones. During oncall rotation, develpers will be calling me, round the clock, to kill their sessions. To resolve this issue, I came up with this package, consisting of a pipelined function and a procedure. The pipelined function, displays the users' sessions in the db. The procedure kills the developer's session only. With the exception, not to kill -the users' current session -other users' sessions -backgroung processes -invalid sessions. I displayed the current sessions' details from my oracle db. connect /as sysdba column sid format 9999 column serial# format 99999 column username format a10 column osuser format a10 column program format a30 column machine format a10 set linesize 100 select s.sid,s.serial#,b.PSERIAL#,s.username, s.osuser,b.name "Name-User", s.program,s.machine from v$process p, v$session s, v$bgprocess b where p.addr = s.paddr and p.addr = b.paddr(+) / SID SERIAL# PSERIAL# USERNAME OSUSER Name- PROGRAM MACHINE ----- ------- ---------- ---------- ---------- ----- ------------------------------ ---------- 335 1 1 oracle PMON oracle@wwwdb22 (PMON) wwwdb22 334 1 1 oracle PSP0 oracle@wwwdb22 (PSP0) wwwdb22 333 1 1 oracle MMAN oracle@wwwdb22 (MMAN) wwwdb22 332 1 1 oracle DBW0 oracle@wwwdb22 (DBW0) wwwdb22 331 1 1 oracle LGWR oracle@wwwdb22 (LGWR) wwwdb22 330 1 1 oracle CKPT oracle@wwwdb22 (CKPT) wwwdb22 329 1 1 oracle SMON oracle@wwwdb22 (SMON) wwwdb22 328 1 1 oracle RECO oracle@wwwdb22 (RECO) wwwdb22 327 1 1 oracle CJQ0 oracle@wwwdb22 (CJQ0) wwwdb22 326 1 1 oracle MMON oracle@wwwdb22 (MMON) wwwdb22 325 1 1 oracle MMNL oracle@wwwdb22 (MMNL) wwwdb22 SID SERIAL# PSERIAL# USERNAME OSUSER Name- PROGRAM MACHINE ----- ------- ---------- ---------- ---------- ----- ------------------------------ ---------- 310 4508 MAHESH oracle sqlplus@wwwdb22 (TNS V1-V3) wwwdb22 314 34 DBSNMP oracle emagent@wwwdb22 (TNS V1-V3) wwwdb22 309 2619 oracle oracle@wwwdb22 (J000) wwwdb22 319 13368 VEEKSHA oracle sqlplus@wwwdb22 (TNS V1-V3) wwwdb22 316 12 DBSNMP oracle emagent@wwwdb22 (TNS V1-V3) wwwdb22 312 15812 VEEKSHA oracle sqlplus@wwwdb22 (TNS V1-V3) wwwdb22 308 21754 SYS oracle sqlplus@wwwdb22 (TNS V1-V3) wwwdb22 324 24468 MANASA oracle sqlplus@wwwdb22 (TNS V1-V3) wwwdb22 19 rows selected. create or replace type myObjectFormat as object(a int, b int, c varchar2(20), d varchar2(20), e varchar2(100)) / create or replace type myTableType as table of myObjectFormat / create or replace package myDemoPack as function prodFunc return myTableType PIPELINED; procedure killmysessionproc(v_sid IN number, v_serial IN number); end; / create or replace package body myDemoPack as function prodFunc return myTableType PIPELINED is begin for c1 in (select s.sid,s.serial#,s.username,s.osuser, substr(sa.sql_text,1,80) sql_txt from v$session s, v$sqlarea sa where s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+) and s.username in sys_context('USERENV', 'SESSION_USER')) loop PIPE ROW (myObjectFormat( c1.sid,c1.serial#,c1.username,c1.osuser, c1.sql_txt )); end loop; return; end; procedure killmysessionproc(v_sid IN number, v_serial IN number) as c_sid number; c_cnt number; c_user varchar2(20); k_user varchar2(20); begin if ((v_sid IS NULL) or (v_sid = '') or (v_serial IS NULL) or (v_serial = '')) then raise_application_error(-20995,'sid/serial# is null'); else begin c_cnt:=0; select count(b.name) into c_cnt from v$process p, v$session s, v$bgprocess b where p.addr = s.paddr and p.addr = b.paddr(+) and s.sid = v_sid and s.serial# = v_serial; exception when others then c_cnt:=0; end; if (c_cnt > 0) then raise_application_error(-20996,'can not kill background processes'); else begin c_cnt:=0; select count(nvl(username,0)) into c_cnt from v$session where sid=v_sid and serial#=v_serial; exception when others then c_cnt:=0; end; if ( c_cnt = 0 ) then raise_application_error(-20997,'username is null/ invalid sid or serial#'); else select distinct sid into c_sid from v$mystat; select sys_context('USERENV', 'SESSION_USER') into c_user from dual; select username into k_user from v$session where sid=v_sid and serial#=v_serial; if ( c_sid = v_sid) then raise_application_error(-20998,'can not kill current session'); elsif (c_user <> k_user) then raise_application_error(-20999,'can not kill others session '); else execute immediate 'alter system kill session '''||v_sid||','||v_serial||''' '; --alter system disconnect session '290,35013' immediate; end if; end if; end if; end if; end; end; / create public synonym myDemoPack for sys.myDemoPack; grant execute on myDemoPack to public; --Created the package, create a public synonym, grante execute privilege to the public connect veeksha/veeksha SQL> select * from table(myDemoPack.prodFunc()); A B C D ---------- ---------- -------------------- -------------------- E -------------------------------------------------------------------------------- 312 15812 VEEKSHA oracle SELECT S.SID,S.SERIAL#,S.USERNAME,S.OSUSER, SUBSTR(SA.SQL_TEXT,1,80) SQL_TXT FRO 319 13376 VEEKSHA oracle SQL> exec myDemoPack.killmysessionproc(329,null) BEGIN myDemoPack.killmysessionproc(329,null); END; * ERROR at line 1: ORA-20995: sid/serial# is null ORA-06512: at "SYS.MYDEMOPACK", line 28 ORA-06512: at line 1 SQL> exec myDemoPack.killmysessionproc(329,'') BEGIN myDemoPack.killmysessionproc(329,''); END; * ERROR at line 1: ORA-20995: sid/serial# is null ORA-06512: at "SYS.MYDEMOPACK", line 28 ORA-06512: at line 1 SQL> exec myDemoPack.killmysessionproc(null,'') BEGIN myDemoPack.killmysessionproc(null,''); END; * ERROR at line 1: ORA-20995: sid/serial# is null ORA-06512: at "SYS.MYDEMOPACK", line 28 ORA-06512: at line 1 --Supply valid sid and serial# to kill a session. SQL> exec myDemoPack.killmysessionproc(325,1) BEGIN myDemoPack.killmysessionproc(325,1); END; * ERROR at line 1: ORA-20996: can not kill background processes ORA-06512: at "SYS.MYDEMOPACK", line 43 ORA-06512: at line 1 SQL> exec myDemoPack.killmysessionproc(312,15812) BEGIN myDemoPack.killmysessionproc(312,15812); END; * ERROR at line 1: ORA-20998: can not kill current session ORA-06512: at "SYS.MYDEMOPACK", line 66 ORA-06512: at line 1 SQL> exec myDemoPack.killmysessionproc(324,24468) BEGIN myDemoPack.killmysessionproc(324,24468); END; * ERROR at line 1: ORA-20999: can not kill others session ORA-06512: at "SYS.MYDEMOPACK", line 68 ORA-06512: at line 1 SQL> exec myDemoPack.killmysessionproc(324,22) BEGIN myDemoPack.killmysessionproc(324,22); END; * ERROR at line 1: ORA-20997: username is null/ invalid sid or serial# ORA-06512: at "SYS.MYDEMOPACK", line 55 ORA-06512: at line 1 --Here the sid and serial# are invalid. SQL> exec myDemoPack.killmysessionproc(319,13376) PL/SQL procedure successfully completed. --Here the user's other session(non-current session) is killed. References: http://www.akadia.com/services/ora_pipe_functions.html http://www.psoug.org/reference/sys_context.html http://www.databasejournal.com/features/oracle/article.php/3636266/Oracle-10g-A-Simple-Security-Approach---Part-1.htm http://www.databasejournal.com/scripts/article.php/3812906/Killing-Sessions.htm