set echo off
-- showuserpid.sql -----------------------------------------------------------------------------------------------------
-- Session - show user session based on unix pid
-- 
-- Usage: @oracle/tuning/showuserpid

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
and spid =   &processid
order by nvl( s.username, s.schemaname ),
         nvl( s.terminal, p.terminal ),
         nvl( s.program, p.program )
;

set echo on