Only a Curious Mind Only a Curious Mind - 6 months ago 17
SQL Question

How to view the amount of active connections in Oracle?

I need to view the amount of active connections in SQL and in Oracle, grouped by program.

To SQL I did this query and works fine:

SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName,
sys.sysprocesses.hostprocess
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame, hostprocess;


I would like to do a query which returns the same results but in Oracle.
I have tried some queries that I found here on StackOverflow but no helps me.

Answer

To get the number of active sessions aggregated by program, you can try:

select COUNT(1), PROGRAM
from v$session
where status = 'ACTIVE'
  GROUP BY PROGRAM

On the same view, you can find much more informations, about sid, user, client, etc.; here you find more informations. Just for the sake of completeness, you have to consider Gv$session on RAC environment