/* ************************************************************* */
/*  Filename         : kill_user.sql - Version X.X		 */
/*  Author           : Rick Kupcunas, Critical Computing USA	 */
/*  Original         : 21-Sep-97				 */
/*  Last Modified    :  -					 */
/*  Descriptnion     : generate the command to kill a user	 */
/*  Usage            : start kill_user.sql <stmt ident>		 */
/*  @C:\rkupcuna05_ora\Oracle\SQL\kill_users.sql                 */
/* ************************************************************* */

break on "OS_USERID" on "DB_USERID" on "SQL STMT" on "SID";

spool sstmt3.rslt;

select  substr(b.osuser, 1, 9)                          "OS_USERID",
        substr(b.username, 1, 9)                        "DB_USERID",
        trunc(a.hash_value/99999)    			"SQL STMT",
        lpad(substr(to_char(b.sid), 1, 5), 5, ' ')      "SID",
        lpad(substr(to_char(b.serial#), 1, 5), 5, ' ')  "SERIAL",
	PROCESS						"PID",
        c.spid                                          "APS PID",
        substr(b.client_info,1,35)				"Workstation:"
from    v$sqlarea a,
        v$session b,
        v$process c
where   b.sql_hash_value = a.hash_value and
        c.addr = b.paddr
order by 3, 1, 2, 4;

set echo off;
set heading off;

select  'To Kill user: '||substr(b.osuser, 1, 9)||' type... alter system kill session '''||b.sid||','||b.serial#||''';'
from    v$sqlarea a,
        v$session b,
        v$process c
where   b.sql_hash_value = a.hash_value and
        c.addr = b.paddr
order by 1;

spool off;
set echo on;
set heading on;

commit;
