Monitoring Active Oracle Sessions

Oracle database sessions can be monitored through system views to track user activity, resource consumption, and connection status. This guide covers essential queries for session management and troubleshooting.

Identifying Active User Connections

The following query provides a comprehensive view of active sessions, including CPU usage and executed SQL statements, sorted by resource consumption:

SET LINESIZE 100
COL name FORMAT A80
SET PAGESIZE 200
SELECT osuser,
       a.username,
       cpu_time/executions/1000000||'s' AS exec_time,
       sql_fulltext,
       machine
FROM v$session a
JOIN v$sqlarea b ON a.sql_address = b.address
ORDER BY cpu_time/executions DESC;

Session Statistics Queries

Basic session counting operations:

-- Total session count
SELECT COUNT(*) FROM v$session;

-- Active concurrent sessions
SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';

-- Session distribution by username
SELECT username, COUNT(username) 
FROM v$session 
WHERE username IS NOT NULL 
GROUP BY username;

User and Privilege Information

-- List all database users
SELECT * FROM all_users;

-- System privileges assigned to users and roles
SELECT * FROM dba_sys_privs;
SELECT * FROM user_sys_privs;

-- Role privileges (limited to current user's roles)
SELECT * FROM role_sys_privs;

-- Object-level permissions
SELECT * FROM dba_tab_privs;
SELECT * FROM all_tab_privs;
SELECT * FROM user_tab_privs;

-- Available database roles
SELECT * FROM dba_roles;

-- Role assignments for users
SELECT * FROM dba_role_privs;
SELECT * FROM user_role_privs;

-- Users with administrative privileges
SELECT * FROM v$pwfile_users;

Cursor Management

-- Open cursors for a specific user
SELECT * FROM v$open_cursor WHERE user_name = 'USER_NAME';

-- Maximum allowed cursors
SELECT value FROM v$parameter WHERE name = 'open_cursors';

System Version and Connection Limits

-- Oracle version information
SELECT banner FROM sys.v_$version;

-- Maximum allowed processes
SELECT value FROM v$parameter WHERE name = 'processes';
SHOW PARAMETER processes;

Configuring Session Limits

The processes parameter directly determines the maximum number of allowed sessions. The sessions parameter adjusts automatically based on the formula: sessions = (1.1 × processes + 5).

Current Configuration Check

SHOW PARAMETER processes;
SHOW PARAMETER sessions;

Modifying Parameters

ALTER SYSTEM SET processes = 300 SCOPE = spfile;
ALTER SYSTEM SET sessions = 335 SCOPE = spfile;

Changes to these parameters require a database restart since they are static parameters.

Connection Statistics

-- Current process count
SELECT COUNT(*) FROM v$process;

-- Current session count
SELECT COUNT(*) FROM v$session;

-- Active session count
SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';

-- Detailed session information
SELECT sid, serial#, username, program, machine, status 
FROM v$session;

Updating Connection Limits

ALTER SYSTEM SET processes = 300 SCOPE = spfile;

After modifying the processes parameter, restart the database:

SHUTDOWN IMMEDIATE;
STARTUP;

Top Resource Consumers

SELECT osuser,
       a.username,
       cpu_time/executions/1000000||'s' AS exec_time,
       sql_fulltext,
       machine
FROM v$session a
JOIN v$sqlarea b ON a.sql_address = b.address
ORDER BY cpu_time/executions DESC;

Platform Consideration: On UNIX systems, each database session maps to a separate operating system process. On Windows platforms, sessions are implemented as threads within a single process.