@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display Exadata CellDisks
*  Parameters : 1 - Cell Name (Use % for wildcard)
*               2 - DiskType  (FlashDisk or HardDisk,Use % for wildcard )
*               3 - DiskName  (Use % for wildcard)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  20-Feb-13  Vishal Gupta  Created
*
*/

DEFINE cell_name="&&1"  
DEFINE diskType="&&2"  
DEFINE name="&&3"  


set term off
COLUMN  _cell_name         NEW_VALUE  cell_name             NOPRINT
COLUMN  _diskType          NEW_VALUE  diskType              NOPRINT
COLUMN  _name              NEW_VALUE  name                  NOPRINT

SELECT DECODE('&&cell_name','','%','&&cell_name')     "_cell_name"
     , DECODE('&&diskType','','%','&&diskType')       "_diskType"
     , DECODE('&&name','','%','&&name')               "_name"
FROM DUAL;
set term on


PROMPT 
PROMPT ********************************************
PROMPT *  E X A D A T A   C E L L    D I S K S
PROMPT *  
PROMPT *  Input Parameter
PROMPT *    - Cell Name  = "&&cell_name"
PROMPT *    - Disk Type  = "&&diskType"
PROMPT *    - Disk Name  = "&&name"
PROMPT *****************************************************


COLUMN cellname                HEADING "CellName"               FORMAT a18 
COLUMN cellDiskName            HEADING "CellDiskName"           FORMAT a22
COLUMN status                  HEADING "Status"                 FORMAT a11
COLUMN creationTime            HEADING "CreationTime"           FORMAT a25
COLUMN deviceName              HEADING "Device|Name"            FORMAT a12
COLUMN devicePartition         HEADING "Device|Partition"       FORMAT a12
COLUMN diskType                HEADING "DiskType"               FORMAT a10
COLUMN celdisksize             HEADING "Size|(GB)"              FORMAT 999,999
COLUMN freeSpace               HEADING "Free|Space|(MB)"        FORMAT 9,999,999
COLUMN id                      HEADING "id"                     FORMAT a40
COLUMN errorCount              HEADING "Error|(#)"              FORMAT 99999
COLUMN interleaving            HEADING "Inter|Leaving"          FORMAT a8
COLUMN lun                     HEADING "Lun"                    FORMAT a5
COLUMN physicalDisk            HEADING "Physical|Disk"          FORMAT a18
COLUMN raidLevel               HEADING "Raid|Level"             FORMAT a5

BREAK ON cellname ON diskType SKIP 1

SELECT clioutput.cellname
     , celldisk.diskType
     , celldisk.name   cellDiskName
     , celldisk.status
     , celldisk.creationTime
     , celldisk.celdisksize/power(1024,3) celdisksize
     , celldisk.freeSpace/power(1024,2) freeSpace
     , celldisk.deviceName
     , celldisk.devicePartition
     , celldisk.errorCount 
     , celldisk.interleaving
     , celldisk.lun
     , celldisk.physicalDisk
     , celldisk.raidLevel
 FROM  v$cell_config cc
     ,  ('/cli-output' PASSING XMLTYPE(cc.confval) 
                   COLUMNS 
                        cellname       VARCHAR2(100) PATH 'context/@cell'
                      , celldisk       XMLTYPE       PATH 'celldisk'
                 ) clioutput
     , XMLTable('/celldisk' PASSING clioutput.celldisk
                   COLUMNS 
                        NAME            VARCHAR2(100) PATH 'name'
                      , creationTime    VARCHAR2(100) PATH 'creationTime'
                      , deviceName      VARCHAR2(100) PATH 'deviceName'
                      , devicePartition VARCHAR2(100) PATH 'devicePartition'
                      , diskType        VARCHAR2(100) PATH 'diskType'
                      , errorCount      NUMBER        PATH 'errorCount'
                      , freeSpace       NUMBER        PATH 'freeSpace'
                      , interleaving    VARCHAR2(100) PATH 'interleaving'
                      , lun             VARCHAR2(100) PATH 'lun'
                      , physicalDisk    VARCHAR2(100) PATH 'physicalDisk'
                      , celdisksize     NUMBER        PATH 'size'
                      , raidLevel       VARCHAR2(100) PATH 'raidLevel'
                      , status          VARCHAR2(100) PATH 'status'
                 ) celldisk
WHERE cc.conftype = 'CELLDISKS'
  AND UPPER(clioutput.cellname) LIKE UPPER('&&cell_name')
  AND UPPER(celldisk.diskType)  LIKE UPPER('&&diskType')
  AND UPPER(celldisk.name)      LIKE UPPER('&&name')
ORDER BY cellname
       , DiskType
       , cellDiskName
;


@@footer