Viewing complete session information

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
--------- ----- ----- ------ ---------- -------- ------------------------------
12923 17 1843 roviad OPS$ORACLE oracle sqlplus@roviadb-01 (TNS V1-V3)
24499 14 7188 search READER root rdbqry_ora@search-02 (TNS V1-V
24846 9 291 search READER root rdbqry_ora@search-01 (TNS V1-V
To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION 'SID, SER#';