See active sessions in PL/SQL

In my current project, we are working with PL/SQL for the client, and in that regard, it’s important to have a variety of tools and scripts at your disposal to help manage and monitor your databases. One area that can often be particularly useful is the ability to track active sessions on your database.

To that end, I have accumulated a small repository of PL/SQL scripts that can help me do just that. These scripts allow me to quickly and easily view information about the sessions currently running on my database, including details such as the username, status, and duration of each session.

One example of a script I use regularly is the “Active Session Overview” script. This script provides a detailed overview of all active sessions on the database, including information such as the username, machine name, and program name associated with each session.

To run the script, I simply connect to my database and execute the PL/SQL code. The script then returns a result set containing the information I need to quickly identify any potential issues or performance bottlenecks.

Overall, having a collection of PL/SQL scripts like this in my toolbox has made it much easier for me to keep tabs on my databases and ensure they are running smoothly. Whether I need to troubleshoot a performance issue or just check in on the overall health of my databases, these scripts are an invaluable resource.

WITH active_sessions AS (
  SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
    s.logon_time, s.last_call_et, s.sql_id, s.sql_child_number, s.sql_exec_start,
    s.sql_exec_id, 
    s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3, s.blocking_session,
    s.wait_class, s.event, s.seconds_in_wait, s.state
  FROM   gv$session s
  WHERE  s.username IS NOT NULL
),
sql_text AS (
  SELECT /*+ materialize */ sql_id, sql_text
  FROM   gv$sql
  WHERE  sql_id IN (SELECT sql_id FROM active_sessions)
)
SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
  s.logon_time, s.last_call_et, s.sql_id, s.sql_child_number, s.sql_exec_start,
  s.sql_exec_id, 
  s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3, s.blocking_session,
  s.wait_class, s.event, s.seconds_in_wait, s.state,
  t.sql_text
FROM   active_sessions s
LEFT JOIN sql_text t ON t.sql_id = s.sql_id
WHERE s.status = 'ACTIVE'
ORDER BY s.logon_time, s.last_call_et

This script will give you the overview of active sessions in your DB with their details, sql_id,wait_class and more.