Donald K. Burleson Oracle Tips

Show Oracle session details

The complexity of the Oracle data dictionary views often makes it very difficult to get useful information quickly out of your system.  The following script will show all session level detail for currently executing sessions within your Oracle system.  This script starts by giving you a count of the current logons, the cumulative number of logons, and the high water mark for logons in your system.

The script then goes on and displays session level detail.  This session level detail includes the process ID, the system ID, SID, the serial number, and the server that this session is currently executing from.

Many Oracle professionals use this script as a starting point and then add additional information.  For example, if you know the ID and serial number for an Oracle session, you can join into the v$sql view and actually get the source code for the SQL statement that is currently executing.

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;

spool off;

SQL> @session

PROD:       current logons=75  cumulative logons=1839      highwater mark=82   

                                dbname Database

                              UNIX/Oracle Sessions

Sessions on database PROD                                                      

Wed Oct 23                                                             page    1

                                dbname Database

                              UNIX/Oracle Sessions

PID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM                       

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

29883     66    7     corp-h APPS       applmgr                                

29885     70    1     corp-h APPS       applmgr                                

3668      68    946   corp-h APPS       applmgr                                

3668      76    1046  corp-h APPS       applmgr  f45runm@corp-hp1 (TNS V1-V3)  

4443      37    488   corp-h APPS       applmgr                                

4443      84    227   corp-h APPS       applmgr  f45runm@corp-hp1 (TNS V1-V3)  

5455      75    1201  corp-h APPS       applmgr                                

5490      74    1392  corp-h APPS       applmgr                                

6381      77    347   ABC\CO APPS       FBurl    S:\ORANT\BIN\F50RUN32.EXE     

6384      78    487   ABC\CO APPS       FBurl    S:\ORANT\BIN\R30RBE32.exe     

6723      81    91    corp-h APPS       applmgr                                

6723      86    58    corp-h APPS       applmgr  f45runm@corp-hp1 (TNS V1-V3)  

6737      72    274   ABC\CO APPS       JJones   S:\ORANT\BIN\F50RUN32.EXE     

6739      71    839   ABC\CO APPS       JJones   S:\ORANT\BIN\R30RBE32.exe     

6947      80    226   ABC\CO APPS       JNunez   S:\ORANT\BIN\R30RBE32.exe     
 

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: