CODEWITHSUNDEEP
sqloracle-database
Only a Curious Mind
Only a Curious Mind

Reputation: 2857

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.

Upvotes: 1

Views: 10583

Answers (2)

Pirate X
Pirate X

Reputation: 3095

select status, count(*) as connections from V$SESSION group by status;

If you need to see who is logged by their username, use this

select username,status, count(*) as connections from V$SESSION where username is not null group by status,username;

OUTPUT FROM MY DATABASE

+----------+---------------+
| STATUS   | CONNECTIONS   |
+----------+---------------+
| ACTIVE   | 104           |
+----------+---------------+
| INACTIVE | 284           |
+----------+---------------+

Here status being ACTIVE means the number of session currently executing SQL

V$SESSION here is the place to look more into it.

Upvotes: 2

Aleksej
Aleksej

Reputation: 22949

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

select COUNT(*), 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

Upvotes: 3

Related Questions