set echo off

-- Name: users_connected.sql
--
--	Description: Users - Display users connected to the database
--              The PID can then be used to terminate/kill the user's session.
--  	          This program is very helpful to clean up locks left hanging after a client machine GPF.
--	
-- Action:      Use the KILL -9 command to terminate the user's PID.
--
-- Usage: @users_connected

set pagesize 32767
set recsep off

column user format a15 word_wrapped
column osuser format a15 word_wrapped
column status format a10 word_wrapped
column program format a15 word_wrapped
column pid format a10 word_wrapped
column logon_time format a10 word_wrapped
column lockwait format a10 word_wrapped
set linesize 133
break on user 

select nvl( s.username, s.schemaname ) "User",
       s.osuser "OSUser",
       s.status,
       nvl( s.terminal, p.terminal ) terminal,
       nvl( s.program, p.program ) program,
       spid "PID", logon_time, 
       lockwait,
       s.sid,
       s.serial#
from v$process p, v$session s
where p.addr = s.paddr
order by nvl( s.username, s.schemaname ),
         nvl( s.terminal, p.terminal ),
         nvl( s.program, p.program )
;

set echo on