System

Sessions

Display
Display by String in SQL 🠈 Useful To Kill a Bad Session
Kill
Limit
Set Current Schema

Overview

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system.

You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Processes vs. Sessions Fun Facts

The PROCESSES parameter should typically be set lower than the SESSIONS parameter. Each database session may require one or more background or foreground processes to support it, but not every session maps 1:1 to a separate OS-level process.

Oracle uses PROCESSES to define the maximum number of OS processes (like server processes, background workers, etc.), while SESSIONS includes all user and internal sessions.

By default, Oracle sets SESSIONS = (1.1 * PROCESSES) + 5, which accounts for this relationship.

It’s best to keep SESSIONS slightly higher than PROCESSES to avoid unexpected errors or limits being hit.

Display

 SELECT resource_name, current_utilization, max_utilization, limit_value 
 FROM v$resource_limit WHERE resource_name in ('sessions', 'processes');

 RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALU
 ------------------------------ ------------------- --------------- ----------
 processes                                      116             140        300
 sessions                                        76             104        480

RAC

-- Show Instance, SID, Serial#, Username and Program

 SELECT s.inst_id,s.sid,s.serial#,p.spid,s.username,s.program
 FROM   gv$session s
 JOIN   gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
 WHERE  s.type != 'BACKGROUND';

-- Show Sessions for a Particular User

 SELECT s.inst_id,s.sid,s.serial#,p.spid,s.username,s.program 
 FROM   gv$session s 
 JOIN   gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id 
 WHERE  s.username='SCOTT';

Non-RAC

-- By SID and Serial#

 SELECT sid,serial#,username,schemaname,osuser,process,machine,
        program,logon_time,status
 FROM v$session
 ORDER BY sid,serial#;

-- By Schema and Username

 SELECT schemaname,username,sid,serial#,osuser,process,
        machine,program,logon_time,status
 FROM v$session
 ORDER BY schemaname,username;

-- By Schema and Username (Active)

 SELECT schemaname,username,sid,serial#,osuser,process,machine,
        program,logon_time,status
 FROM v$session
 WHERE status='ACTIVE'
 ORDER BY schemaname,username;

Display By String in SQL

PROMPT
ACCEPT v_string PROMPT 'String in SQL [SHRINK SPACE]: ' DEFAULT 'SHRINK SPACE'
ACCEPT v_hours  PROMPT 'Last n Hours [8]: '             DEFAULT '8'
PROMPT

COL sample_time     FORMAT a30
COL sql_id          FORMAT a20
COL SESSION_ID      FORMAT 999999999
COL SESSION_SERIAL# FORMAT999999999
COL username        FORMAT a15
COL machine         FORMAT a25
COL sql_text        FORMAT a35

-- Process
SELECT h.sample_time
, h.sql_id
, h.SESSION_ID
, h.SESSION_SERIAL#
, u.username
, h.machine
, SUBSTR(s.sql_text,1,35) SQL
FROM dba_hist_active_sess_history h
INNER JOIN v$sql s          ON s.sql_id = h.sql_id
LEFT OUTER JOIN dba_users u ON  u.user_id = h.user_id
WHERE s.sql_text like '%&v_string%'
AND sample_time > SYSDATE - &v_hours/24
ORDER BY h.sample_time DESC;

Kill a Session

Method 1

session.getKillInfo.sql

Method 2

Get Session Info

 COL program FORMAT a35
 SELECT s.inst_id,s.sid,s.serial#,p.spid,s.username,s.program 
 FROM   gv$session s 
 JOIN   gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id 
 WHERE  s.username='&Schema';

RAC

ALTER SYSTEM KILL SESSION 'sid,serial_number,@inst_id' [IMMEDIATE];

 ALTER SYSTEM KILL SESSION '103,2345,@2' IMMEDIATE;

Non-RAC

ALTER SYSTEM KILL SESSION 'sid,serial_number' [IMMEDIATE];

 ALTER SYSTEM KILL SESSION '103,2345' IMMEDIATE;

Limit Maxmimum Concurrent Sessions

Create profile setting the SESSIONS_PER_USER parameter.
Assign users to the new profile.

 CREATE PROFILE max_conns_25 LIMIT SESSIONS_PER_USER 25;
 ALTER USER scott PROFILE max_conns_25;

Set Current Schema

ALTER SESSION SET CURRENT_SCHEMA = UserName;

 ALTER SESSION SET CURRENT_SCHEMA = scott;

You can only do what your user account has privs for.

<- System