Report how long a session has been idle in Oracle

Summary

Instructions provided describe how to list all connected sessions and report how long the sessions have been idle (the length of time since the session last executed a SQL statement).

Note:
ArcSDE software, including the application server, command tools, and SDK with C and Java APIs, was deprecated at ArcGIS 10.2.2 and is no longer distributed. ArcGIS software features were deprecated, rather than immediately removed, to provide customers with backward compatibility and give as much advance notice as possible to adopt newer technology.

Procedure

1. Connect in SQL*Plus as either the SYS or SYSTEM user and execute the anonymous PL/SQL procedure.

Code:
SQL> DECLARE
  2  
  3    CURSOR session_cursor IS 
  4      SELECT username, sid, last_call_et
  5      FROM v$session
  6      WHERE username IS NOT NULL AND username NOT IN ('SYS','SYSTEM')
  7      ORDER BY last_call_et;
  8  
  9    num_mins        NUMBER;
 10    num_mins_sec    NUMBER;
 11    wait_secs       NUMBER;
 12    num_hours       NUMBER;
 13    num_hours_min   NUMBER;
 14    wait_mins       NUMBER;
 15    num_days        NUMBER;
 16    num_days_hours  NUMBER;
 17    wait_hours      NUMBER;
 18    wait_char_mins  VARCHAR2(4);
 19    wait_char_secs  VARCHAR2(4);
 20  
 21  BEGIN
 22  
 23    DBMS_OUTPUT.PUT_LINE(chr(10));
 24  
 25    FOR idle_time IN session_cursor LOOP
 26  
 27    -- Total number of seconds waited...
 28  
 29      num_mins := trunc(idle_time.last_call_et/60);
 30      num_mins_sec := num_mins * 60;
 31      wait_secs := idle_time.last_call_et - num_mins_sec;
 32  
 33    -- Total number of minutes waited...
 34  
 35      num_hours := trunc(num_mins/60);
 36      num_hours_min := num_hours * 60;
 37      wait_mins := num_mins - num_hours_min;
 38  
 39    -- Total number of hours waited...
 40  
 41      num_days := trunc(num_hours/24);
 42      num_days_hours := num_days * 24;
 43      wait_hours := num_hours - num_days_hours;
 44  
 45      DBMS_OUTPUT.PUT('User '||idle_time.USERNAME||'('||idle_time.SID||') has been idle for '||num_days||' day(s) '||wait_hours||':');
 46    
 47      IF wait_mins < 10 THEN
 48        wait_char_mins := '0'||wait_mins||'';
 49        DBMS_OUTPUT.PUT(''||wait_char_mins||':');
 50       ELSE
 51        DBMS_OUTPUT.PUT(''||wait_mins||':');
 52      END IF;
 53  
 54      IF wait_secs < 10 THEN
 55        wait_char_secs := '0'||wait_secs||'';
 56        DBMS_OUTPUT.PUT(''||wait_char_secs||'');
 57      ELSE
 58        DBMS_OUTPUT.PUT(''||wait_secs||'');
 59      END IF;
 60    
 61      DBMS_OUTPUT.NEW_LINE;
 62  
 63    END LOOP;
 64  
 65  END;
 66  /

User usdomain\tb(145) has been idle for 0 day(s) 0:54:46
User AGSSERVER(152) has been idle for 0 day(s) 0:54:46
User SNOOPY(151) has been idle for 1 day(s) 8:56:03
User SDE(137) has been idle for 3 day(s) 3:17:16

PL/SQL procedure successfully completed.