Monitor open connections in oracle

13. July 2011 04:08 by Mrojas in General  //  Tags: , , ,   //   Comments (0)

 

This is an useful script taken from: http://dbaforums.org/oracle/index.php?showtopic=16834 which provides a list of the currently
open sessions.

To track if you application is having problems releasing connection you can open the SQLPlusW and run this script
a couple of times while you exercise your application.

set line 150;
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,20) computer,
       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;

This will provide an output like: