REM  #*****************************************************
REM  # File Name: su.sql
REM  #
REM  # Purpose: Who is logged on, their SID for sid_sql, 
REM  #          their PID, SQL_ADDR and time logged on
REM  #
REM  #*****************************************************

column program format a30 trunc
column sql_address format a8
column sid format 9999
column username format a10 trunc
column spid format a5
column sql_hash_value format 9999999999 heading hash_val
column status format a1 heading act
column process heading 'Remote|PID'
column LAST_CALL_ET heading 'Secs since|Last exec' format 9,999,999
column last_exec heading 'Time since|Last exec' format a10

cle bre

set linesize 145

select s.sid, s.username, s.sql_address, s.sql_hash_value, s.program, p.spid, s.process, s.osuser,
--       decode(s.status,'ACTIVE','Y','INACTIVE','N','CACHED','C',s.status) status, 
       logon_time, server,
       lpad(mod(trunc(LAST_CALL_ET/60/60),60),2,'0') ||':'||
       lpad(mod(trunc(LAST_CALL_ET/60),60),2,'0')    ||':'||
       lpad(mod(LAST_CALL_ET,60),2,'0')||decode(s.status,'ACTIVE',' *',null) last_exec
from v$session s, v$process p
where s.username is not null
and s.paddr = p.addr
order by last_exec
/