CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Welsh
Welsh

Reputation: 5468

Query Active & Inactive Connections based off Machine and Username

I'm attempting to make a query that lists a count of Active, Inactive & Total number of connections per machine, per username in an Oracle 10g database.

I have the following Query:

SELECT count(1) AS con_count, machine, username 
FROM v$session 
WHERE type <> 'BACKGROUND'
GROUP BY username, machine 
ORDER BY con_count DESC;

That will list a count of the # of total connections per machine per username but am having trouble adapting it to do a count of Active, Inactive & Total connections as an all in one query.

So results should be something like:

ACTIVE   INACTIVE   TOTAL   MACHINE   USERNAME

And I can make the query do one of the first three columns but not all three at once.

Any help would be appreciated, as my SQL is very, very rusty.

Upvotes: 1

Views: 10924

Answers (2)

BellevueBob
BellevueBob

Reputation: 9618

Assuming the column type can be used to distinguish your status, you can use a SUM expression:

 select sum(case when status = 'ACTIVE' then 1 else 0 end) as ACTIVE_COUNT
      , sum(case when status = 'INACTIVE' then 1 else 0 end) as INACTIVE_COUNT
      , count(*) as TOTAL_COUNT
      , username, machine
 from   v$session
 where type <> 'BACKGROUND'
 group by username, machine
 order by username, machine

Updated based on Nicholas's response that the column status should be used.

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

Column status of v$session view contains information about active and inactive sessions. So you could recode you query as follows:

SELECT s.machine
     , s.username 
     , count(decode(s.STATUS, 'ACTIVE', 1))   as active_con
     , count(decode(s.STATUS, 'INACTIVE', 1)) as inactive_con
     , count(*)                               as total_con
FROM v$session s
WHERE type <> 'BACKGROUND'
GROUP BY username, machine 
ORDER BY total_con DESC;

Upvotes: 7

Related Questions