SQL Script to Check the User wise Count in Oracle database

Here is the SQL script

set lines 120 pages 1000
break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a30
select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
       count(*) TOT,
       COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
       COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;

advertisements

 

Sample Output

SQL> USERNAME			      TOT     ACTIVE   INACTIVE
------------------------------ ---------- ---------- ----------
SYS					2	   1	      1
INTERNAL			       47	  47	      0
TEST					1	   1	      0
			       ---------- ---------- ----------
sum				       50	  49	      1