session_time.sql

-- see all in one line
SET LINESIZE 300
SELECT
   SUBSTR(s.username,1,5) USERNAME,
   SUBSTR(s.sid,1,4) SID,
   SUBSTR(s.serial#,1,4) SERIAL#,
   SUBSTR(s.status,1,3) STATUS,
   substr(s.machine,1,8) machine,
   SUBSTR(s.osuser,1,8) 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,
   substr('0'||trunc(last_call_et/86400),-2,2)  || ':'  ||
  substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
  substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
  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
  AND p.addr(+) = s.paddr
ORDER BY username, sid;