@@header

/*
*
*  Author    : Vishal Gupta
*  Purpose   : ASM Disk Count
*  Parameter : NONE
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  12-Jul-11  Vishal Gupta  Created
*
*
*/

/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE DISKGROUP_NAME
DEFINE DISKGROUP_NAME="&&1"  

set term off
COLUMN  _DISKGROUP_NAME          NEW_VALUE  DISKGROUP_NAME             NOPRINT

SELECT UPPER(DECODE('&&DISKGROUP_NAME','','%','&&DISKGROUP_NAME'))   "_DISKGROUP_NAME"
FROM DUAL;
set term on

PROMPT *******************************************
PROMPT A S M    D I S K    C O U N T  
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - DiskGroup Name = '&&DISKGROUP_NAME'
PROMPT *******************************************

COLUMN DiskGroup                 HEAD "DiskGroup"                   FORMAT A20

SELECT dg.NAME DiskGroup
     , d.FAILGROUP
     , d.MOUNT_STATUS 
     , d.HEADER_STATUS
     , d.MODE_STATUS  
     , d.STATE        
     , COUNT(1) DiskCOunt 
 -- V$ASM_DISK_STAT does not perform disk discovery like V$ASM_DISK view.
 FROM v$asm_disk_stat d 
    , v$asm_diskgroup_stat dg   
WHERE d.group_number = dg.group_number 
  AND dg.name like '&&DISKGROUP_NAME'
GROUP BY dg.NAME 
     , d.FAILGROUP
     , d.MOUNT_STATUS 
     , d.HEADER_STATUS
     , d.MODE_STATUS  
     , d.STATE  
ORDER BY dg.NAME 
     , d.FAILGROUP  
;


@@footer