OracleSessions
Session count by user and machine
Who is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN inst_id HEADING "INST" FORMAT 9994COLUMN username FORMAT A205COLUMN machine FORMAT A35 TRUNCATE6COLUMN total HEADING "SESSIONS" FORMAT 99,9907COLUMN active HEADING "ACTIVE" FORMAT 99,9908 9SELECT inst_id, username, machine,10 COUNT(*) AS total,11 SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active12FROM gv$session13WHERE type = 'USER'14GROUP BY inst_id, username, machine15ORDER BY total DESC;Save it as ora-session-counts.sql and run it with SQL> @ora-session-counts.
Helps with
- ORA-00018: maximum number of sessions exceeded
- ORA-00020: maximum number of processes (%s) exceeded
- ORA-12519: TNS:no appropriate service handler found
Part of these runbooks
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…
- Active user sessionsUser 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…
- 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.