Oracle v$session query

Most Oracle professionals know that they can query the v$session view to see the current sessions. However, you can also interrogate the v$sysstat view to see the current logons, cumulative logons since startup, and the high-water-mark of logons since startup time.

The following script provides a wealth of information about the number of sessions on your Oracle database.

rem session.sql - displays all connected sessions

set echo off;

set termout on;

set linesize 80;

set pagesize 60;

set newpage 0;

select

rpad(c.name||':',11)||rpad(' current logons='||

(to_number(b.sessions_current)),20)||'cumulative logons='||

rpad(substr(a.value,1,10),10)||'highwater mark='||

b.sessions_highwater Information

from

v$sysstat a,

v$license b,

v$database c

where

a.name = 'logons cumulative'

;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;

select 'Sessions on database '||substr(name,1,8) from v$database;

set heading on;

select

substr(a.spid,1,9) pid,

substr(b.sid,1,5) sid,

substr(b.serial#,1,5) ser#,

substr(b.machine,1,6) box,

substr(b.username,1,10) username,

-- b.server,

substr(b.osuser,1,8) os_user,

substr(b.program,1,30) program

from v$session b, v$process a

where

b.paddr = a.addr

and type='USER'

order by spid;

ttitle off;

set heading off;

select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',

''''||'SID, SER#'||''''||';' from dual;

spool off;

Here is the listing from this script. Note that we see complete information about the four current sessions on this database, and we see that there have been 212,199 logons since database startup, with a high-water mark of 13 sessions.

SQL> @session

INFORMATION

----------------------------------------------------------------------------

PRODC1: current logons=4 cumulative logons=212199 highwater mark=13

Wed Jul 04 page 1

dbname Database

UNIX/Oracle Sessions

Sessions on database PRODC1

Wed Jul 04 page 1

dbname Database

UNIX/Oracle Sessions

PID SID SER# BOX USERNAME OS_USER PROGRAM