D.H - Logging

Kill Idle Sessions for E-Business Suite Application Users( 刪除超過三小時的)

SELECT   DECODE(TRUNC(SYSDATE - logon_time), 0, NULL, TRUNC(SYSDATE - logon_time) || ' Days' || ' + ')
         || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - logon_time, 1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
         v$session.SID,
         v$session.serial#,
         v$process.spid spid,
         v$session.process unix_appl,
         v$session.username,
         status,
         osuser,
         machine,
         v$session.program,
         module,
         action,
         sql_hash_value,
         'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.serial# || '''' || ' immediate;' kill_sql
    FROM v$session, v$process
   WHERE (v$session.paddr = v$process.addr)
     AND (v$session.status = 'INACTIVE')
     AND (v$session.username = 'APPS')
     AND (v$session.last_call_et / 3600 > 3)
     --AND (v$session.action LIKE 'FRM%')                                                   
     --AND v$session.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
ORDER BY logon_time ASC;

Reference:

• DBA Tips for Killing Oracle Idle Session!!