Skip to content
Oracle DBA Scripts & Database Utilities

Active Session Analysis (gvsess.sql)

What This Script Does

Section titled “What This Script Does”

Displays active Oracle sessions across all RAC instances with comprehensive session information including SQL IDs, elapsed times, and transaction details. This script combines session information with session metrics for enhanced monitoring capabilities.

The Script

Section titled “The Script”
rem  gvsess.sql
rem
ttitle 'Active Oracle Sessions'
rem
set linesize 1000
rem
col inst_id format 99 heading 'RAC|INST'
col sid format 99990 heading 'SID'
col serial# format 99990 heading 'SER'
col sid_serial format a10 heading 'SID,SERIAL#'
col machine format a30
col user# format 990 heading 'UID'
col username format a15 heading 'USERNAME'
col service_name format a10 heading 'SERVICE'
col osuser format a8 heading 'OS|USERNAME'
col command format 990 heading 'COMMAND'
col taddr format a8 heading 'TADDR'
col lockwait format a8 heading 'LOCKWAIT'
col seconds_in_wait format b999999 heading 'SECONDS|IN WAIT'
col status format a8 heading 'STATUS'
col server format a9 heading 'SERVER'
col module format a22 heading 'MODULE/PROGRAM'
col %CPU format 999.99 heading 'CPU|%'
col last_call_et format 999,999 heading 'ELAPSED|SECS|LAST|CALL'
col failover_type format a9 heading 'FAILOVER|TYPE'
col failover_mode format a9 heading 'FAILOVER|METHOD'
col failed_over format a6 heading 'FAILED'
col sql_hash_value FORMAT   9999999999999999 HEADING  'SQL|Hash Value'
col sql_id heading 'SQL ID'
col event format a29 heading 'EVENT'
col parameters format a54 heading 'PARAMETERS'
col obj_file_block_row_wait format a30
col sql format a150
rem
select s.inst_id
     , s.sid||','||s.serial# sid_serial
     , s.username
     , s.osuser
     , substr(s.service_name,1,10) service_name
     , s.machine
     , s.status
     , s.LAST_CALL_ET
     , s.SQL_ID
     , PREV_SQL_ID
  from gv$session s
left join dba_objects o
       on s.row_wait_obj# = o.object_id
     , gv$sessmetric m
 where s.inst_id = m.inst_id
   and s.sid = m.session_id
   and s.serial# = m.SERIAL_NUM
   and s.inst_id             like nvl('&inst_id','%')
   and s.sid                 like nvl('&sid','%')
   and s.username            like upper(nvl('&user','%'))
   and (s.status='ACTIVE' or s.TADDR is not null)
   and s.type <> 'BACKGROUND'
 order by
       s.last_call_et desc,
       s.inst_id,
       s.sid
/

Usage

Section titled “Usage”
-- Basic usage - show all active sessions
@gvsess.sql
-- Enter: [press enter for all instances]
-- Enter: [press enter for all sessions]
-- Enter: [press enter for all users]


-- Filter by specific RAC instance
@gvsess.sql
-- Enter: 1 (for instance 1)


-- Filter by specific session
@gvsess.sql
-- Enter: [press enter for all instances]
-- Enter: 1234 (for specific SID)


-- Filter by username
@gvsess.sql
-- Enter: [press enter for all instances]
-- Enter: [press enter for all sessions]
-- Enter: SCOTT (for specific user)

Parameters

Section titled “Parameters”

The script prompts for:

  • inst_id: RAC instance ID (% for all instances)
  • sid: Session ID (% for all sessions)
  • user: Username (% for all users)

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT on GV$SESSION
SELECT on GV$SESSMETRIC
SELECT on DBA_OBJECTS

Sample Output

Section titled “Sample Output”
                                Active Oracle Sessions


RAC SID,SERIAL# USERNAME        OS       SERVICE    MACHINE                        STATUS   ELAPSED LAST SQL ID       PREV_SQL_ID
INS             #               USERNAME            CALL                                    SECS
--- ----------- --------------- -------- ---------- ------------------------------ -------- ----------- ------------- -------------
  1 1234,5678   SCOTT          oracle   MYSERVICE  prodserver01.company.com      ACTIVE          45 abc123def456  xyz789abc123
  2 2345,6789   HR_USER        webapp   HRSERVICE  appserver02.company.com       ACTIVE          12 def456ghi789  abc123def456
  1 3456,7890   SALES_USER     java     SALESVC    webserver03.company.com       ACTIVE           8 ghi789jkl012  def456ghi789

Key Output Columns

Section titled “Key Output Columns”
  • RAC INST: RAC instance number
  • SID,SERIAL#: Session identifier and serial number
  • USERNAME: Database username
  • OS USERNAME: Operating system username
  • SERVICE: Service name used for connection
  • MACHINE: Client machine name
  • STATUS: Session status (ACTIVE, INACTIVE)
  • ELAPSED SECS LAST CALL: Seconds since last activity
  • SQL ID: Current SQL statement identifier
  • PREV_SQL_ID: Previous SQL statement identifier

Common Use Cases

Section titled “Common Use Cases”

Active Session Monitoring

-- Monitor all currently active sessions
@gvsess.sql
-- Enter: [appropriate values for filtering]

RAC Load Distribution

-- Check session distribution across RAC instances
@gvsess.sql
-- Analyze instance column for load balancing

Long Running Session Detection

-- Identify sessions with high LAST_CALL_ET values
@gvsess.sql
-- Look for sessions with long elapsed times

User Activity Analysis

-- Monitor specific user activity
@gvsess.sql
-- Enter specific username to filter

Performance Impact

Section titled “Performance Impact”
  • Low Impact: Queries system views with minimal overhead
  • RAC Aware: Efficiently queries all instances simultaneously
  • Filtering: Input parameters help reduce result set size
Section titled “Related Scripts”