Sessions
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.
Default derived as: (1.1 * PROCESSES) + 5
.
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;
Kill a Session
Method 1
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.