English

How To: 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).

Procedure

The following anonymous PL/SQL procedure reports each session and how long the session has been idle (if the time reported is greater than 0).

Executing the procedure requires SELECT privileges on the V$SESSION table. Either execute the anonymous PL/SQL procedure as the SYS or SYSTEM user in SQL*Plus.

  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.


    The output shows user usdomain\tb, with Oracle sid (145), has been idle for 0 days, 54 minutes and 46 seconds, whereas the SDE user, Oracle sid (137), has been idle for 3 days, 3 hours, 17 minutes and 16 seconds.

    Understanding how long a session has been idle may be valuable in helping to determine whether the session needs to be killed, or if the user is not being productive.