Show all the Oracle users connected to the database

SQL Code

 1set lines 100 pages 999
 2col ID format a15
 3col USERNAME format a20
 4select username
 5,      sid || ',' || serial# "ID"
 6,      status
 7,      last_call_et "Last Activity"
 8from   v$session
 9where  username is not null
10order by status desc
11,        last_call_et desc
12/

Sample Oracle Output:

 1USERNAME             ID              STATUS   Last Activity
 2-------------------- --------------- -------- -------------
 3ORDS_PUBLIC_USER     286,58632       INACTIVE          1709
 4ORDS_PUBLIC_USER     52,56550        INACTIVE          1708
 5ORDS_PUBLIC_USER     53,20873        INACTIVE          1708
 6SYS                  15,1554         ACTIVE            1843
 7SYS                  237,34880       ACTIVE              81
 8SYS                  36,39003        ACTIVE               0
 9
106 rows selected.

Purpose:

• To monitor and display information about active user sessions within the Oracle database, focusing on their status and recent activity levels. This aids in understanding database usage patterns, identifying potential issues, and proactively managing database resources.

Breakdown:

Formatting Output:

• set lines 100 pages 999: Adjusts display settings to accommodate potentially lengthy results.
• col ID format a15, col USERNAME format a20: Formats columns for enhanced readability.

Querying Session Data:

• select username, sid || ',' || serial# "ID", status, last_call_et "Last Activity" from v$session: Retrieves specific information from the v$session view:

  • username: Identifies the database user associated with each session.
  • sid || ',' || serial# "ID": Concatenates session ID and serial number for unique identification.
  • status: Indicates the current status of each session (e.g., ACTIVE, INACTIVE).
  • last_call_et "Last Activity": Shows the time elapsed since the last database call from each session, indicating activity level.

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

Ordering Results:

• order by status desc, last_call_et desc: Presents results in a prioritized order:

  • First, sorts sessions by status in descending order, highlighting those with potentially problematic statuses (e.g., ACTIVE, INACTIVE).
  • Within each status group, further sorts sessions by last_call_et in descending order, emphasizing those with longer periods of inactivity.

Key Points:

• User-Centric Focus: Targets sessions with known usernames, providing a clear view of user activity.
• Session Status and Activity: Highlights session statuses and inactivity durations, aiding in identifying potential issues or resource management needs.
• Prioritization: Emphasizes sessions with statuses that might require attention and those with longer inactivity periods for potential intervention.

Insights and Explanations:

• Troubleshooting:

  • Identifying sessions with unexpected statuses (e.g., INACTIVE for extended periods) can help pinpoint potential problems or resource bottlenecks.

• Monitoring:

  • Tracking session activity patterns can inform resource planning and proactive issue prevention.

• Resource Management:

  • Understanding session activity can aid in optimizing resource allocation and preventing performance issues.

• Customization:

  • The code can be modified to filter for specific users, statuses, or activity thresholds based on analysis needs.

• Integration: Consider incorporating this code into regular database monitoring and reporting processes for proactive management.