Dev Corner

Show the Longest 10 Active User Sessions

baobab under Oracle DBA (Database Administration)

The following SQL can be used by Oracle Database Administrator (DBA) to retrieve information about the currently active sessions with longest execution. The SQL statement is designed to run with SQL*Plus.

To show a list with top current sessions with longest acivity execute in SQL*Plus:

/*
  longest10active.sqlplus.sql
  shows the top 10 active sessions with longest execution activity
  execute in SQL*Plus
*/
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
SET linesize 132
SET verify off
 
SELECT * FROM (
   SELECT TO_CHAR(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
          s.sid, s.STATUS,
          floor(last_call_et/60) "LastCallET",
          s.username, s.osuser,
          p.spid, s.module || ' - ' || s.program uprogram,
          s.machine, s.sql_hash_value
     FROM v$session s, v$process p
    WHERE p.addr = s.paddr
          AND s.type = 'USER'
          AND module IS NOT NULL
          AND s.STATUS = 'ACTIVE'
    ORDER BY 4 DESC)
WHERE ROWNUM < 11;

If you want the execute the statement in different tool, you can remove the formatting clauses:

/*
  longest10active.sql
  shows the top 10 active sessions with longest execution activity
*/
SELECT * FROM (
   SELECT TO_CHAR(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
          s.sid, s.STATUS,
          floor(last_call_et/60) "LastCallET",
          s.username, s.osuser,
          p.spid, s.module || ' - ' || s.program uprogram,
          s.machine, s.sql_hash_value
     FROM v$session s, v$process p
    WHERE p.addr = s.paddr
          AND s.type = 'USER'
          AND module IS NOT NULL
          AND s.STATUS = 'ACTIVE'
    ORDER BY 4 DESC)
WHERE ROWNUM < 11;