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;

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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google