Oracle sessions sorted by logon time

Track idle time for user sessions to uncover potential resource leaks, optimize database performance, and maintain security best practices.

SQL Code

1et lines 100 pages 999
2select username
3,      floor(last_call_et / 60) "Minutes"
4,      status
5from   v$session
6where  username is not null
7order by last_call_et
8/

Sample Oracle Output:

 1USERNAME             OSUSER          ID              STATUS   LOGIN_TIME     LAST_CALL_ET
 2-------------------- --------------- --------------- -------- -------------- ------------
 3SYS                  oracle          54,58456        ACTIVE   20:09 10/12/23            0
 4SYS                  oracle          52,61761        ACTIVE   21:37 10/12/23          100
 5SYS                  oracle          15,1554         ACTIVE   23:34 09/12/23        79495
 6ORDS_PUBLIC_USER     oracle          286,58632       INACTIVE 23:36 09/12/23        79361
 7ORDS_PUBLIC_USER     oracle          53,20873        INACTIVE 23:36 09/12/23        79360
 8
 95 rows selected.
10SQL>

Purpose:

• To monitor user session activity by identifying idle sessions and their durations, aiding in resource management and potential troubleshooting.

Breakdown:

Formatting Output:

• set lines 100 pages 999: Adjusts display settings to accommodate potentially lengthy results.

Querying Session Data:

• select username, floor(last_call_et / 60) "Minutes", status from v$session: Retrieves specific information from the v$session view, which provides details about active user sessions:

  • username: Identifies the user associated with each session.
  • floor(last_call_et / 60) "Minutes": Calculates the approximate duration of inactivity in minutes since the last call from each session.
  • status: Indicates the current status of each session (e.g., ACTIVE, INACTIVE).

• where username is not null: Filters results to include only sessions with known usernames, excluding system-level sessions.

• order by last_call_et: Presents results in ascending order of idle time, highlighting the longest-idle sessions first.

Key Points:

• Idle Session Focus: Targets sessions that haven't made database calls recently, potentially indicating inactive users or processes.
• Inactivity Duration: Calculates idle time in minutes for easy interpretation.
• Session Status: Provides context about whether sessions are currently active or inactive.
• User-Oriented View: Presents information in a user-friendly format, aiding in understanding session activity patterns.

Insights and Explanations:

• Resource Management: Identifying idle sessions can help optimize resource usage by potentially terminating or reducing resources for inactive sessions.

• Troubleshooting:

  • Long-idle sessions might indicate issues such as hung processes or users forgetting to log out.
  • Analyzing idle sessions can help identify potential problems and take corrective actions.

• Monitoring:

  • Regularly monitoring idle sessions can help proactively manage database resources and prevent performance issues.

• Customization:

  • The code can be modified to adjust idle time thresholds for defining "idle" sessions or to filter for specific users or session statuses based on analysis needs.