ablog

不器用で落着きのない技術者のメモ

特定のユーザで接続しているセッションを kill する PL/SQL スクリプト

Oracle 
define user_name = &user_name

set heading off
set pagesize 0
set linesize 10000
set trimout on
set trimspool on
set feedback off

set serveroutput on size 1000000

declare
	cursor cu is select sid, serial#, username, machine, osuser, program, status from v$session where username = upper('&&user_name');
	sql_stmt varchar2(200);
begin
	for rec in cu loop
		begin
			sql_stmt := 'alter system kill session '''||rec.sid||','||rec.serial#||'''';
			dbms_output.put_line(rec.sid||', '||rec.serial#||', '||rec.username||', '||rec.machine||', '||rec.osuser||', '||rec.program||', '||rec.status);
			execute immediate sql_stmt;
		exception when others then
			dbms_output.put_line(rec.sid||','||rec.serial#||':'||sqlcode||':'||sqlerrm);
		end;
	end loop;
end;
/

exit

実行してみるとこんな感じ

$ sqlplus system/manager @kill_sessions_by_username.sql 

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 17:05:35 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enter value for user_name: SCOTT
old   2:   cursor cu is select sid, serial#, username, machine, osuser, program, status from v$session where username = upper('&&user_name');
new   2:   cursor cu is select sid, serial#, username, machine, osuser, program, status from v$session where username = upper('SCOTT');
235, 303, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
236, 14, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
238, 1343, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
240, 510, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
242, 3257, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
246, 1832, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
255, 7011, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
258, 3872, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
259, 2512, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
268, 56017, SCOTT, dev01, tomcat, JDBC Thin Client, INACTIVE
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options