понедельник, 31 июля 2017 г.

Determine How Long a Session has been Idle using v$session

Following SQL using information from LAST_CALL_ET:
select s.*
--select 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' kill_serial_sid
  from (
        SELECT  s.sid,s.serial#,s.username
                ,s.status
                ,substr(s.machine,1,10)
                ,s.osuser,s.module
                ,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
                -- idle time
                ,to_dsinterval(
                  -- 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
       ) s
 where 1 = 1
   and s.status = 'INACTIVE'
   and s.idle_time >= to_dsinterval('1 00:00:00')
 ORDER BY idle_time desc;
Note: LAST_CALL_ET - If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

Search Idle Sessions using filter in Enterprise Manager

You can specify search criteria using WHERE clause

as following:
1 = 1
and username IS NOT NULL
and status = 'INACTIVE'
and to_dsinterval(
      -- 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)
    ) >= to_dsinterval('1 00:00:00')