whologin.sql

SET LINESIZE 200
        SELECT
           substr(s.username,1,8) username
          ,substr(s.sid,1,3) sid
          ,s.serial#
          ,s.status
          ,substr(s.machine, 1,8) machine
          ,substr(s.osuser,1,5) osuser
          ,substr(s.program,1,20) client_program
          ,s.process client_process
          ,substr(p.program,1,20) server_program
          ,to_char(p.spid) spid
          ,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
           -- idle time
           -- days separately
          ,substr('0'||trunc(last_call_et/86400),-2,2)  || ':'  ||
           -- hours
           substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
           -- minutes
           substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
           --seconds
           substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)  idle_time
        FROM v$session s, v$process p
        WHERE s.username IS NOT NULL
          -- use outer join to show sniped sessions in
          -- v$session that don't have an OS process
          AND p.addr(+) = s.paddr
        ORDER BY
           username
          ,sid
/