SQL> select cellname, 2 name, 3 deviceName, 4 diskType, 5 round(freeSpace/power(1024,3), 2) freeSpace, 6 round(disk_size/power(1024,3), 2) disk_size 7 from ( 8 select cellname, XMLTYPE.createXML(confval) confval 9 from v$cell_config 10 where conftype='CELLDISKS' 11 and cellname='192.168.12.3' 12 ) v, 13 xmltable('/cli-output/celldisk' passing v.confval 14 columns 15 name varchar(15) path 'name', 16 creationtime varchar(25) path 'creationTime', 17 deviceName varchar(9) path 'deviceName', 18 devicePartition varchar2(10) path 'devicePartition', 19 diskType varchar2(9) path 'diskType', 20 errorCount number path 'errorCount', 21 freeSpace number path 'freeSpace', 22 id varchar2(50) path 'id', 23 interleaving varchar(10) path 'interleaving', 24 lun varchar2(5) path 'lun', 25 raidLevel number path 'raidLevel', 26 disk_size number path 'size', 27 status varchar2(10) path 'status' 28 ); CELLNAME NAME DEVICENAME DISKTYPE FREESPACE DISK_SIZE --------------- --------------- ---------- --------- ---------- ---------- 192.168.12.3 CD_00_enkcel01 /dev/sda HardDisk 0 1832.59 192.168.12.3 CD_01_enkcel01 /dev/sdb HardDisk 0 1832.59 192.168.12.3 CD_02_enkcel01 /dev/sdc HardDisk 0 1861.7 192.168.12.3 CD_04_enkcel01 /dev/sdl HardDisk 0 1861.7 192.168.12.3 CD_05_enkcel01 /dev/sde HardDisk 0 1861.7 192.168.12.3 CD_06_enkcel01 /dev/sdf HardDisk 0 1861.7 192.168.12.3 CD_07_enkcel01 /dev/sdg HardDisk 0 1861.7 192.168.12.3 CD_08_enkcel01 /dev/sdh HardDisk 0 1861.7 192.168.12.3 CD_09_enkcel01 /dev/sdi HardDisk 0 1861.7 192.168.12.3 CD_10_enkcel01 /dev/sdj HardDisk 0 1861.7 192.168.12.3 CD_11_enkcel01 /dev/sdk HardDisk 0 1861.7 192.168.12.3 FD_00_enkcel01 /dev/sds FlashDisk 0 22.88 192.168.12.3 FD_01_enkcel01 /dev/sdr FlashDisk 0 22.88 192.168.12.3 FD_02_enkcel01 /dev/sdt FlashDisk 0 22.88 192.168.12.3 FD_03_enkcel01 /dev/sdu FlashDisk 0 22.88 192.168.12.3 FD_04_enkcel01 /dev/sdaa FlashDisk 0 22.88 192.168.12.3 FD_05_enkcel01 /dev/sdz FlashDisk 0 22.88 192.168.12.3 FD_06_enkcel01 /dev/sdab FlashDisk 0 22.88 192.168.12.3 FD_07_enkcel01 /dev/sdac FlashDisk 0 22.88 192.168.12.3 FD_08_enkcel01 /dev/sdn FlashDisk 0 22.88 192.168.12.3 FD_09_enkcel01 /dev/sdo FlashDisk 0 22.88 192.168.12.3 FD_10_enkcel01 /dev/sdp FlashDisk 0 22.88 192.168.12.3 FD_11_enkcel01 /dev/sdq FlashDisk 0 22.88 192.168.12.3 FD_12_enkcel01 /dev/sdv FlashDisk 0 22.88 192.168.12.3 FD_13_enkcel01 /dev/sdw FlashDisk 0 22.88 192.168.12.3 FD_14_enkcel01 /dev/sdx FlashDisk 0 22.88 192.168.12.3 FD_15_enkcel01 /dev/sdy FlashDisk 0 22.88 27 rows selectedOf course, the same approach can be applied to get other information out (like Grid Disks, etc.).
Wednesday, July 18, 2012
V$CELL_CONFIG
If you ever wondered how you can convert XML information in v$cell_config on Exadata database servers into relational rows and columns so you can get a nice view of the cell configuration without going into the cell itself then here is a nice piece of SQL which does the job. I'm using celldisks as an example and selecting only a number of column so the output fits nicely on the screen:
Subscribe to:
Post Comments (Atom)
Why free space is reported as zero ?
ReplyDeleteIs it really 0 ?
Regards
GregG
GregG,
ReplyDeletethe free space on the cell disks is what's left after grid disks were allocated, i.e. it's not free space the same way database sees it.