转自http://www.orafaq.com/files/KillingMySessions_1.txt

			   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
ITPub博客

killing my session in oracle

最新文章
