113 lines
4.4 KiB
MySQL
113 lines
4.4 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata LUNs
|
|
* Parameters : 1 - Cell Name (Use % for wildcard)
|
|
* 2 - DiskType (FlashDisk or HardDisk,Use % for wildcard )
|
|
* 3 - LUN Name (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 L U N S
|
|
PROMPT **************************************************
|
|
|
|
|
|
COLUMN cellname HEADING "CellName" FORMAT a18
|
|
COLUMN diskType HEADING "Disk|Type" FORMAT a10
|
|
COLUMN name HEADING "LUN|Name" FORMAT a5
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN lunSize HEADING "Size|(GB)" FORMAT 999,999
|
|
COLUMN cellDisk HEADING "CellDisk|Name" FORMAT a22
|
|
COLUMN deviceName HEADING "deviceName" FORMAT a12
|
|
COLUMN id HEADING "id" FORMAT a10
|
|
COLUMN isSystemLun HEADING "isSystemLun" FORMAT a5
|
|
COLUMN lunAutoCreate HEADING "lunAutoCreate" FORMAT a6
|
|
COLUMN lunUID HEADING "lunUID" FORMAT a6
|
|
COLUMN physicalDrives HEADING "physicalDrives" FORMAT a10
|
|
COLUMN raidLevel HEADING "Raid|Level" FORMAT a6
|
|
COLUMN lunWriteCacheMode HEADING "lunWriteCacheMode" FORMAT a61
|
|
|
|
BREAK ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF lunSize FORMAT 99,999,999,999 ON REPORT
|
|
|
|
SELECT clioutput.cellname
|
|
, lun.diskType
|
|
, lun.name
|
|
, lun.status
|
|
, ROUND(lun.lunSize/power(1000,3)) lunSize
|
|
, lun.cellDisk
|
|
, lun.deviceName
|
|
, lun.id
|
|
, lun.isSystemLun
|
|
, lun.lunAutoCreate
|
|
, lun.lunUID
|
|
, lun.physicalDrives
|
|
, lun.raidLevel
|
|
, lun.lunWriteCacheMode
|
|
FROM v$cell_config cc
|
|
, XMLTable('/cli-output' PASSING XMLTYPE(confval)
|
|
COLUMNS
|
|
cellname VARCHAR2(100) PATH 'context/@cell'
|
|
, lun XMLTYPE PATH 'lun'
|
|
) clioutput
|
|
, XMLTable('/lun' PASSING clioutput.lun
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH 'name'
|
|
, cellDisk VARCHAR2(100) PATH 'cellDisk'
|
|
, deviceName VARCHAR2(100) PATH 'deviceName'
|
|
, diskType VARCHAR2(100) PATH 'diskType'
|
|
, id VARCHAR2(100) PATH 'id'
|
|
, isSystemLun VARCHAR2(100) PATH 'isSystemLun'
|
|
, lunAutoCreate VARCHAR2(100) PATH 'lunAutoCreate'
|
|
, lunSize NUMBER PATH 'lunSize'
|
|
, lunUID VARCHAR2(100) PATH 'lunUID'
|
|
, physicalDrives VARCHAR2(100) PATH 'physicalDrives'
|
|
, raidLevel VARCHAR2(100) PATH 'raidLevel'
|
|
, lunWriteCacheMode VARCHAR2(100) PATH 'lunWriteCacheMode'
|
|
, status VARCHAR2(100) PATH 'status'
|
|
) lun
|
|
WHERE cc.conftype = 'LUNS'
|
|
AND UPPER(clioutput.cellname) LIKE UPPER('&&cell_name')
|
|
AND UPPER(lun.diskType) LIKE UPPER('&&diskType')
|
|
AND UPPER(lun.name) LIKE UPPER('&&name')
|
|
ORDER BY clioutput.cellname
|
|
, lun.diskType
|
|
, lun.deviceName
|
|
;
|
|
|
|
|
|
|
|
@@footer |