122 lines
4.9 KiB
MySQL
122 lines
4.9 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata GridDisks
|
|
* 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
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE cell_name
|
|
UNDEFINE diskType
|
|
UNDEFINE name
|
|
|
|
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 G R I D 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 name HEADING "GridDiskName" FORMAT a30
|
|
COLUMN status HEADING "Status" FORMAT a10
|
|
COLUMN creationTime HEADING "Creation|Time" FORMAT a10 TRUNC
|
|
COLUMN diskType HEADING "DiskType" FORMAT a10
|
|
COLUMN asmDiskgroupName HEADING "ASM|Diskgroup|Name" FORMAT a10
|
|
COLUMN asmDiskName HEADING "ASM DiskName" FORMAT a30
|
|
COLUMN asmDiskgroupName HEADING "ASM|Diskgroup|Name" FORMAT a15
|
|
COLUMN asmFailGroupName HEADING "ASM|FailGroup|Name" FORMAT a16
|
|
COLUMN availableTo HEADING "Avail|able|To" FORMAT a10
|
|
COLUMN cachingPolicy HEADING "Caching|Policy" FORMAT a10
|
|
COLUMN offset HEADING "Offset|(GB)" FORMAT 999,999
|
|
COLUMN griddisksize HEADING "Size|(GB)" FORMAT 999,999
|
|
COLUMN virtualSize HEADING "Virtual|Size|(GB)" FORMAT 999,999
|
|
COLUMN errorCount HEADING "Error|(#)" FORMAT 99999
|
|
COLUMN sparse HEADING "Sparse" FORMAT a10
|
|
|
|
|
|
SELECT clioutput.cellname
|
|
, griddisk.diskType
|
|
, griddisk.name
|
|
, griddisk.status
|
|
, griddisk.asmDiskgroupName
|
|
, griddisk.asmDiskName
|
|
, griddisk.asmFailGroupName
|
|
, griddisk.availableTo
|
|
, griddisk.cachingPolicy
|
|
, griddisk.creationTime
|
|
, griddisk.errorCount
|
|
, griddisk.offset/power(1024,3) offset
|
|
, griddisk.griddisksize/power(1024,3) griddisksize
|
|
, griddisk.virtualSize/power(1024,3) virtualSize
|
|
, griddisk.sparse
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
cellname VARCHAR2(100) PATH 'context/@cell'
|
|
, griddisk XMLTYPE PATH 'griddisk'
|
|
) clioutput
|
|
, XMLTable('/griddisk' PASSING clioutput.griddisk
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, asmDiskgroupName VARCHAR2(100) PATH 'asmDiskgroupName'
|
|
, asmDiskName VARCHAR2(100) PATH 'asmDiskName'
|
|
, asmFailGroupName VARCHAR2(100) PATH 'asmFailGroupName'
|
|
, availableTo VARCHAR2(100) PATH 'availableTo'
|
|
, cachingPolicy VARCHAR2(100) PATH 'cachingPolicy'
|
|
, creationTime VARCHAR2(100) PATH 'creationTime'
|
|
, diskType VARCHAR2(100) PATH 'diskType'
|
|
, errorCount NUMBER PATH 'errorCount'
|
|
, offset NUMBER PATH 'offset'
|
|
, griddisksize NUMBER PATH 'size'
|
|
, virtualSize NUMBER PATH 'virtualSize'
|
|
, status VARCHAR2(100) PATH 'status'
|
|
, sparse VARCHAR2(100) PATH 'sparse'
|
|
) griddisk
|
|
WHERE cc.conftype = 'GRIDDISKS'
|
|
AND UPPER(clioutput.cellname) LIKE UPPER('&&cell_name')
|
|
AND UPPER(griddisk.diskType) LIKE UPPER('&&diskType')
|
|
AND UPPER(griddisk.name) LIKE UPPER('&&name')
|
|
ORDER BY clioutput.cellname
|
|
, griddisk.diskType
|
|
, offset
|
|
, griddisk.name
|
|
;
|
|
|
|
|
|
|
|
@@footer |