Current running sessions

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Every connection to the Oracle database engine is a Session. Every login requires a session. Even internal database processes (jobs) are part of the Oracle session list.

There are many types of processes in Oracle -- background processes like SMON, PMON, RECO, ARCH, CKPT, DBWR, etc..  And user processes like dedicated servers or shared server

A session is a specific connection of a user to an Oracle instance through a user process. For example, when a user starts SQL*Plus, the user must provide a valid user name and password, and then a session is established for that user. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

/* TO IDENTIFY MINIMUM AND MAXIMUM PROCESSES AND SESSIONS CONNECTED COUNT FROM DATABASE STARTUP */

SET LINESIZE 132
SELECT * FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('processes','sessions');

One more thing: You normally do *not* set the sessions parameter within the init.ora file. Just the processes parameter. Sessions is auto-set and calculated as a multiplier of processes. (Default value - Derived: (1.1 * PROCESSES) + 5)

Also there are times when it is useful to be able to locate and kill Oracle sessions that are locking database resources.

/* THE FOLLOWING SQL WILL PRINT AN OVERVIEW OF SESSIONS CURRENTLY ACTIVE */
SELECT
   RPAD(D.NAME||':',11)||RPAD(' CURRENT LOGONS='||
   (TO_NUMBER(L.SESSIONS_CURRENT)),20)||'CUMULATIVE LOGONS='||
   RPAD(SUBSTR(SS.VALUE,1,10),10)||'HIGHWATER MARK='||
   L.SESSIONS_HIGHWATER INFORMATION
FROM
   V$SYSSTAT SS,
   V$LICENSE L,
   V$DATABASE D
WHERE
   SS.NAME = 'logons cumulative';

/* ACTIVE SESSIONS DETAILS SPECIFIED DB USERNAME YOU ARE LOOKING FOR OR PRESS ENTER KEY FOR ALL USERS */

SET PAGESIZE 50;
SET LINESIZE 300;

COLUMN "SID" FORMAT 99999999
COLUMN "SERIAL" FORMAT 99999999
COLUMN "OS ID" FORMAT A12
COLUMN "OSUSER" FORMAT A12
COLUMN "LOGON TIME" FORMAT A18
COLUMN "DB USERNAME" FORMAT A12
COLUMN "STATUS" FORMAT A9

BREAK ON "LOGON TIME" ON "OS ID" ON "OSUSER" ON "DB USERNAME" ON "SID" ON "SERIAL" ON "SQL ID" ON "STATUS";

SELECT TO_CHAR(B.LOGON_TIME,'DD-MON-YYYY HH24:MI') "LOGON TIME",
       C.SPID "OS ID",
       B.OSUSER "OSUSER",
       B.USERNAME "DB USERNAME",
       B.SID "SID",
       B.SERIAL# "SERIAL",
       A.SQL_ID "SQL ID",
       B.STATUS "STATUS",
       A.SQL_TEXT "SQL TEXT"
FROM V$SQLTEXT A, V$SESSION B, V$PROCESS C
WHERE A.ADDRESS = B.SQL_ADDRESS
  AND B.PADDR = C.ADDR
  AND A.HASH_VALUE = B.SQL_HASH_VALUE
  AND B.STATUS = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
  AND B.USERNAME LIKE UPPER('%&DATABASE_USERNAME%')
  ORDER BY C.SPID, A.HASH_VALUE, A.PIECE
/

/* ACTIVE SESSIONS DETAILS SPECIFIED OS USERNAME YOU ARE LOOKING FOR OR PRESS ENTER KEY FOR ALL OS USERS CONNECTED TO DATABASE */

SET PAGESIZE 50;
SET LINESIZE 300;

COLUMN "SID" FORMAT 99999999
COLUMN "SERIAL" FORMAT 99999999
COLUMN "OS ID" FORMAT A12
COLUMN "OSUSER" FORMAT A12
COLUMN "LOGON TIME" FORMAT A18
COLUMN "DB USERNAME" FORMAT A12
COLUMN "STATUS" FORMAT A9

BREAK ON "LOGON TIME" ON "OS ID" ON "OSUSER" ON "DB USERNAME" ON "SID" ON "SERIAL" ON "SQL ID" ON "STATUS";

SELECT TO_CHAR(B.LOGON_TIME,'DD-MON-YYYY HH24:MI') "LOGON TIME",
       C.SPID "OS ID",
       B.OSUSER "OSUSER",
       B.USERNAME "DB USERNAME",
       B.SID "SID",
       B.SERIAL# "SERIAL",
       A.SQL_ID "SQL ID",
       B.STATUS "STATUS",
       A.SQL_TEXT "SQL TEXT"
FROM V$SQLTEXT A, V$SESSION B, V$PROCESS C
WHERE A.ADDRESS = B.SQL_ADDRESS
  AND B.PADDR = C.ADDR
  AND A.HASH_VALUE = B.SQL_HASH_VALUE
  AND B.STATUS = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
  AND B.OSUSER LIKE '%&OS_USERNAME%'
  ORDER BY C.SPID, A.HASH_VALUE, A.PIECE
/

/* ACTIVE SESSIONS DETAILS SPECIFIED DB SID YOU ARE LOOKING FOR OR PRESS ENTER KEY FOR ALL SID'S */

SET PAGESIZE 50;
SET LINESIZE 300;

COLUMN "SID" FORMAT 99999999
COLUMN "SERIAL" FORMAT 99999999
COLUMN "OS ID" FORMAT A12
COLUMN "OSUSER" FORMAT A12
COLUMN "LOGON TIME" FORMAT A18
COLUMN "DB USERNAME" FORMAT A12
COLUMN "STATUS" FORMAT A9

BREAK ON "LOGON TIME" ON "OS ID" ON "OSUSER" ON "DB USERNAME" ON "SID" ON "SERIAL" ON "SQL ID" ON "STATUS";

SELECT TO_CHAR(B.LOGON_TIME,'DD-MON-YYYY HH24:MI') "LOGON TIME",
       C.SPID "OS ID",
       B.OSUSER "OSUSER",
       B.USERNAME "DB USERNAME",
       B.SID "SID",
       B.SERIAL# "SERIAL",
       A.SQL_ID "SQL ID",
       B.STATUS "STATUS",
       A.SQL_TEXT "SQL TEXT"
FROM V$SQLTEXT A, V$SESSION B, V$PROCESS C
WHERE A.ADDRESS = B.SQL_ADDRESS
  AND B.PADDR = C.ADDR
  AND A.HASH_VALUE = B.SQL_HASH_VALUE
  AND B.STATUS = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
  AND B.SID LIKE '%&DB_SID%'
  ORDER BY C.SPID, A.HASH_VALUE, A.PIECE
/

/* ACTIVE SESSIONS DETAILS SPECIFIED OS PID YOU ARE LOOKING FOR OR PRESS ENTER KEY FOR ALL OS ID'S CONNECTED TO DATABASE */

SET PAGESIZE 50;
SET LINESIZE 300;

COLUMN "SID" FORMAT 99999999
COLUMN "SERIAL" FORMAT 99999999
COLUMN "OS ID" FORMAT A12
COLUMN "OSUSER" FORMAT A12
COLUMN "LOGON TIME" FORMAT A18
COLUMN "DB USERNAME" FORMAT A12
COLUMN "STATUS" FORMAT A9

BREAK ON "LOGON TIME" ON "OS ID" ON "OSUSER" ON "DB USERNAME" ON "SID" ON "SERIAL" ON "SQL ID" ON "STATUS";

SELECT TO_CHAR(B.LOGON_TIME,'DD-MON-YYYY HH24:MI') "LOGON TIME",
       C.SPID "OS ID",
       B.OSUSER "OSUSER",
       B.USERNAME "DB USERNAME",
       B.SID "SID",
       B.SERIAL# "SERIAL",
       A.SQL_ID "SQL ID",
       B.STATUS "STATUS",
       A.SQL_TEXT "SQL TEXT"
FROM V$SQLTEXT A, V$SESSION B, V$PROCESS C
WHERE A.ADDRESS = B.SQL_ADDRESS
  AND B.PADDR = C.ADDR
  AND A.HASH_VALUE = B.SQL_HASH_VALUE
  AND B.STATUS = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
  AND C.SPID LIKE '%&OS_PID%'
  ORDER BY C.SPID, A.HASH_VALUE, A.PIECE
/

/* ACTIVE SESSIONS DETAILS SPECIFIED SQL ID YOU ARE LOOKING FOR OR PRESS ENTER KEY FOR ALL SQL ID'S */

SET PAGESIZE 50;
SET LINESIZE 300;

COLUMN "SID" FORMAT 99999999
COLUMN "SERIAL" FORMAT 99999999
COLUMN "OS ID" FORMAT A12
COLUMN "OSUSER" FORMAT A12
COLUMN "LOGON TIME" FORMAT A18
COLUMN "DB USERNAME" FORMAT A12
COLUMN "STATUS" FORMAT A9

BREAK ON "LOGON TIME" ON "OS ID" ON "OSUSER" ON "DB USERNAME" ON "SID" ON "SERIAL" ON "SQL ID" ON "STATUS";

SELECT TO_CHAR(B.LOGON_TIME,'DD-MON-YYYY HH24:MI') "LOGON TIME",
       C.SPID "OS ID",
       B.OSUSER "OSUSER",
       B.USERNAME "DB USERNAME",
       B.SID "SID",
       B.SERIAL# "SERIAL",
       A.SQL_ID "SQL ID",
       B.STATUS "STATUS",
       A.SQL_TEXT "SQL TEXT"
FROM V$SQLTEXT A, V$SESSION B, V$PROCESS C
WHERE A.ADDRESS = B.SQL_ADDRESS
  AND B.PADDR = C.ADDR
  AND A.HASH_VALUE = B.SQL_HASH_VALUE
  AND B.STATUS = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
  AND A.SQL_ID LIKE '%&SQL_ID%'
  ORDER BY C.SPID, A.HASH_VALUE, A.PIECE
/