Killing My Sessions In Oracle

KILLING MY SESSIONS IN ORACLE Author JP Vijaykumar Date Nov 14th 2009 Manay 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 MACHINE ----- ------- ---------- ---------- --------------------------------------- ---------335 1 1 oracle wwwdb22 334 1 1 oracle wwwdb22 333 1 1 oracle wwwdb22 332 1 1 oracle wwwdb22 331 1 1 oracle wwwdb22 330 1 1 oracle wwwdb22 329 1 1 oracle wwwdb22 328 1 1 oracle

Name- PROGRAM ----PMON

oracle@wwwdb22 (PMON)

PSP0

oracle@wwwdb22 (PSP0)

MMAN

oracle@wwwdb22 (MMAN)

DBW0

oracle@wwwdb22 (DBW0)

LGWR

oracle@wwwdb22 (LGWR)

CKPT

oracle@wwwdb22 (CKPT)

SMON

oracle@wwwdb22 (SMON)

RECO

oracle@wwwdb22 (RECO)

wwwdb22 327 wwwdb22 326 wwwdb22 325 wwwdb22

1

1

oracle

CJQ0

oracle@wwwdb22 (CJQ0)

1

1

oracle

MMON

oracle@wwwdb22 (MMON)

1

1

oracle

MMNL

oracle@wwwdb22 (MMNL)

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 else

((v_sid IS NULL) or (v_sid = '') or (v_serial IS NULL) or (v_serial = ''))then raise_application_error(-20995,'sid/serial# is null'); 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 session');

( c_sid = v_sid) then raise_application_error(-20998,'can not kill current

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-ASimple-Security-Approach---Part-1.htm http://www.databasejournal.com/scripts/article.php/3812906/Killing-Sessions.htm