Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

 

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
see code depot for full script
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#';

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.