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:
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName,
dbid > 0
dbid, loginame, hostprocess;
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