@@header

set term off
/*
*
*  Author    : Vishal Gupta
*  Purpose   : ASM DiskGroup Info
*  Parameter : NONE
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  ------------------------------------------------
*  13-Feb-14  Vishal Gupta  Added following additional fields in the output
*                              - sector_size,block_size,voting_files
*  13-Dec-12  Vishal Gupta  Added additional query to display ASM attributes
*  27-Jan-10  Vishal Gupta  Created
*
*/
set term on


SET numf 999,999

DEFINE size_label=GB
DEFINE size_divider="1024/1024/1024"



PROMPT 
PROMPT ************************************************
PROMPT * A S M    D I S K G R O U P    D E T A I L S  *
PROMPT ************************************************

COLUMN diskgroup_name            HEAD "DiskGroup"                   FORMAT A16
COLUMN group_number              HEAD "G#"                          FORMAT 99
COLUMN type                      HEAD "Redundancy|Type"             FORMAT A6
COLUMN compatibility             HEAD "ASM|Compat"                  FORMAT A10
COLUMN database_compatibility    HEAD "RDBMS|Compat"                FORMAT A10
COLUMN allocation_unit_size_MB   HEAD "AU|Size|(MB)"                FORMAT 999
COLUMN sector_size               HEAD "Sector|Size"                 FORMAT 999999
COLUMN block_size                HEAD "Block|Size"                  FORMAT 99,999 
COLUMN offline_disks             HEAD "Offline|Disks"
COLUMN voting_files              HEAD "Voting|Files"                FORMAT a6 
COLUMN TOTAL_GB                  HEAD "(A)||Total|Avail|(&size_label)"
COLUMN used_GB                   HEAD "(B)||Total|Used|(&size_label)"
COLUMN hot_used_GB               HEAD "Hot|Used|(&size_label)"
COLUMN cold_used_GB              HEAD "Cold|Used|(&size_label)"
COLUMN FREE_GB                   HEAD "(C)||Total|Free|(&size_label)"
COLUMN REQUIRED_MIRROR_FREE_GB   HEAD "(D)|Required|Free|ForMirror|(&size_label)"  JUSTIFY RIGHT
COLUMN mirrored_total_GB         HEAD "(E)||Mirrored|Avail|(&size_label)"
COLUMN mirrored_used_GB          HEAD "(F)||Mirrored|Used|(&size_label)"
COLUMN mirrored_free_GB          HEAD "Mirrored|Free|(&size_label)"
COLUMN USABLE_GB                 HEAD "(C-D)/REDUN||Usable|Free|(&size_label)"

BREAK ON REPORT 
COMPUTE SUM LABEL 'Total' OF TOTAL_GB                 FORMAT 99,999,999  ON REPORT 
COMPUTE SUM LABEL 'Total' OF USED_GB                  FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF HOT_USED_GB              FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF COLD_USED_GB             FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF FREE_GB                  FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB  FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF MIRRORED_TOTAL_GB        FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF MIRRORED_USED_GB         FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF MIRRORED_FREE_GB         FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF USABLE_GB                FORMAT 9,999,999 ON REPORT 

SELECT d.NAME diskgroup_name
  , d.group_number
  , d.state
  , d.type
  , d.compatibility
  , d.database_compatibility
  , d.allocation_unit_size/1024/1024 allocation_unit_size_MB
  , d.sector_size
  , d.block_size
  , d.offline_disks
  , d.voting_files
 FROM v$asm_diskgroup_stat d;
 
PROMPT 
PROMPT ******************************************************
PROMPT * A S M    D I S K G R O U P    A T T R I B U T E S  *
PROMPT ******************************************************

COLUMN attribute                HEAD "Attribute"             FORMAT A25  
COLUMN system_created           HEAD "System|Created"        FORMAT A10
COLUMN read_only                HEAD "Read|Only"             FORMAT A5
COLUMN value                    HEAD "Value"                 FORMAT A15
 
SELECT d.name diskgroup_name
     , a.name attribute
     , DECODE(a.system_created,'N','No','Y','Yes',a.read_only) system_created
     , DECODE(a.read_only,'N','No','Y','Yes',a.read_only) read_only
     , a.value 
 FROM v$asm_diskgroup_stat d
    , v$asm_attribute a
 WHERE a.group_number = d.group_number  
   AND a.name not like 'template%'
 ORDER BY attribute
        , diskgroup_name
 ;

 
@@footer