Tuesday, April 22, 2008

SQL Script to view and count number of sessions to the database

set pages 100
set lines 3000

col machine format a25
col username format a15
col program format a25
break on 1;

set heading off;
select
'Sessions on database '|| d.name ||' having instance name '|| i.instance_name
from v$database d,v$instance i;
set heading on;

compute sum label 'Total Sessions' of sessions on 1
select
1,username,machine,program,count(*) sessions
from v$session
group by username,machine,program
order by username,sessions desc;

No comments: