racstats.sql

--==================================================================
--
-- Title       :   racstats.sql
-- Description :   To list counts of active & inactive users 
--                 across a RAC environment. 
--
-- Usage/Notes :
--
-- Copyright   :   ABCdba.com 2010
--
--==================================================================
 
COL ai FOR 99999   HEA 'Instance ID'
COL bi FOR 999,999 HEA 'Active'
COL ci FOR 999,999 HEA 'Inactive'
COL di FOR 999,999 HEA 'Total'
 
SELECT
   DISTINCT( a.inst_id ) ai, 
   b.countid bi, 
   c.countid ci, 
   b.countid+c.countid di
FROM
   gv$session a,
   (
      SELECT
         inst_id,
         status,
         COUNT(status) countid
      FROM
         gv$session
      WHERE
         status = 'ACTIVE'
      GROUP BY
         inst_id, status
   ) b,
   (
      SELECT
         inst_id,
         status,
         COUNT(status) countid
      FROM
         gv$session
      WHERE
         status = 'INACTIVE'
      GROUP BY
         inst_id, status
   ) c
WHERE
   a.inst_id = b.inst_id
AND 
   a.inst_id = c.inst_id
ORDER BY
   a.inst_id
/

© copyright 2001-2014 ABCdba.com | all rights reserved