111 lines
4.5 KiB
MySQL
111 lines
4.5 KiB
MySQL
@@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 |