session_all.sql (No Background process)

-- ************************************************************************************************************
-- * Filename           : ses_all.sql
-- * Author             : Saravanakumar K
-- * Create             : 13-Aug-2018
-- * Last Update        : 13-Aug-2018
-- * Description        : It will provide database all session information exclueding background process
-- * Usage              : start ses_all.sql
-- * Version            : v1
-- *************************************************************************************************************
set pagesize 9999
set lines 700 tab off feedback on
col ses_info     for a12    heading 'SID,SER#|SesInfo'
col program      for a25    heading Program trunc
col LOGON-TIME   for a17    heading 'Logon-Time'
col machine      for a15    heading 'Machine/|Server Name'      
col username     for a15    heading 'User Name'                
col osuser       for a10    heading 'OS user'
col PROCESS      for a8     heading 'Server|Proc ID'  
col cl_proc      for a15    heading 'Client|Proc ID'
col sql_id       for a15    heading 'SQL ID'
col last_call_et for a16    heading 'Last UserCall|Act/Inact'    
col STATUS       for a10    heading 'Session|Status'
SELECT
 c.inst_id,
 SUBSTR(c.sid||','||c.serial#,1,12) ses_info,
 SUBSTR(c.username,1,15) USERNAME,
 SUBSTR(c.program,1,25) PROGRAM,
 SUBSTR(c.osuser,1,10) osuser,
 SUBSTR(d.spid,1,9) PROCESS,
 SUBSTR(c.machine,1,15) MACHINE,
 c.sql_id sql_id,
 c.status status,
     trim(to_char(trunc(c.last_call_et / 60 / 60 / 24),'09'))||' '
    || trim(to_char(trunc(mod(c.last_call_et / 60 / 60, 24)),'09'))||':'
    || trim(to_char(trunc(mod(c.last_call_et, 3600) / 60),'09'))||':'
    || trim(to_char(mod(mod(c.last_call_et, 3600), 60),'09'))  last_call_et,
 TO_CHAR(logon_time,'dd-mm-yy hh24:mi:ss') "LOGON-TIME"
FROM
 gv$session c,
 gv$process d
WHERE
 c.paddr=d.addr
 and c.inst_id=d.inst_id
 and type <> 'BACKGROUND'
 and c.sql_id is not null
ORDER BY
 c.inst_id,
 logon_time,
 c.program;
set tab on feedback on
clear col