OracleSessions

Active user sessions

User sessions doing work right now on every instance: who, from which OS user and machine, the module, what they're waiting on, the SQL_ID, when they logged on and how long they've been in the current call (HH:MM:SS). Your own session is left out.

All RAC instances

Not yet verified. How scripts are tested

b-ora-active.sql

1CLEAR COLUMNS2SET LINESIZE 250 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN inst       HEADING "INST" FORMAT 9904COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A145COLUMN username   FORMAT A206COLUMN osuser     FORMAT A15 TRUNCATE7COLUMN machine    FORMAT A25 TRUNCATE8COLUMN module     FORMAT A25 TRUNCATE9COLUMN event      FORMAT A30 TRUNCATE10COLUMN sql_id     FORMAT A1311COLUMN logged_on  HEADING "LOGGED ON" FORMAT A1712COLUMN in_state   HEADING "IN STATE" FORMAT A1013 14SELECT s.inst_id AS inst,15       s.sid || ',' || s.serial# AS sid_serial,16       s.username, s.osuser, s.machine, s.module, s.event, s.sql_id,17       TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI') AS logged_on,18       TO_CHAR(TRUNC(s.last_call_et / 3600), 'FM9900') || ':' ||19       TO_CHAR(TRUNC(MOD(s.last_call_et, 3600) / 60), 'FM00') || ':' ||20       TO_CHAR(MOD(s.last_call_et, 60), 'FM00') AS in_state21FROM gv$session s22WHERE s.type = 'USER'23  AND s.status = 'ACTIVE'24  AND NOT (s.inst_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'INSTANCE'))25           AND s.sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID')))26ORDER BY s.last_call_et DESC;

CLEAR COLUMNS
SET LINESIZE 250 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN inst       HEADING "INST" FORMAT 990
COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A14
COLUMN username   FORMAT A20
COLUMN osuser     FORMAT A15 TRUNCATE
COLUMN machine    FORMAT A25 TRUNCATE
COLUMN module     FORMAT A25 TRUNCATE
COLUMN event      FORMAT A30 TRUNCATE
COLUMN sql_id     FORMAT A13
COLUMN logged_on  HEADING "LOGGED ON" FORMAT A17
COLUMN in_state   HEADING "IN STATE" FORMAT A10

SELECT s.inst_id AS inst,
       s.sid || ',' || s.serial# AS sid_serial,
       s.username, s.osuser, s.machine, s.module, s.event, s.sql_id,
       TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI') AS logged_on,
       TO_CHAR(TRUNC(s.last_call_et / 3600), 'FM9900') || ':' ||
       TO_CHAR(TRUNC(MOD(s.last_call_et, 3600) / 60), 'FM00') || ':' ||
       TO_CHAR(MOD(s.last_call_et, 60), 'FM00') AS in_state
FROM gv$session s
WHERE s.type = 'USER'
  AND s.status = 'ACTIVE'
  AND NOT (s.inst_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'INSTANCE'))
           AND s.sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID')))
ORDER BY s.last_call_et DESC;

Save it as b-ora-active.sql and run it with SQL> @b-ora-active.

Open in denrepo

Part of these runbooks

The database is slow

More Oracle scripts: Sessions

Long operations with time remaining (all instances)Long operations still running on any instance, with percent done and elapsed, remaining and estimated total minutes. RMAN is left out because it has…
SQL active for 30 minutes or more (all instances)Active application sessions whose current call has run for at least 1,800 seconds, with instance, program, OS process ID, SQL_ID, plan hash value and…
Session count by user and machineWho is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
Everything about one sessionPrompts for a SID and shows the user, OS details, program, current and previous SQL_ID, wait event and the OS process ID behind it.
Find the session behind an OS process IDWhen top or ps shows an Oracle process burning CPU, enter its PID to see which session and SQL it belongs to.
Long-running operations in progressFull scans, sorts, RMAN and index builds that Oracle tracks in V$SESSION_LONGOPS, with percent done and estimated seconds remaining.