oracledba.help
System

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;

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

  1. Create profile setting the SESSIONS_PER_USER parameter.
  2. 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