System

Sessions

Display
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.

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

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