106 lines
4.3 KiB
SQL
106 lines
4.3 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : ASM DiskGroup Info
|
|
* Parameter : NONE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------
|
|
* 13-Feb-14 Vishal Gupta Added following additional fields in the output
|
|
* - sector_size,block_size,voting_files
|
|
* 13-Dec-12 Vishal Gupta Added additional query to display ASM attributes
|
|
* 27-Jan-10 Vishal Gupta Created
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
|
|
SET numf 999,999
|
|
|
|
DEFINE size_label=GB
|
|
DEFINE size_divider="1024/1024/1024"
|
|
|
|
|
|
|
|
PROMPT
|
|
PROMPT ************************************************
|
|
PROMPT * A S M D I S K G R O U P D E T A I L S *
|
|
PROMPT ************************************************
|
|
|
|
COLUMN diskgroup_name HEAD "DiskGroup" FORMAT A16
|
|
COLUMN group_number HEAD "G#" FORMAT 99
|
|
COLUMN type HEAD "Redundancy|Type" FORMAT A6
|
|
COLUMN compatibility HEAD "ASM|Compat" FORMAT A10
|
|
COLUMN database_compatibility HEAD "RDBMS|Compat" FORMAT A10
|
|
COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 999
|
|
COLUMN sector_size HEAD "Sector|Size" FORMAT 999999
|
|
COLUMN block_size HEAD "Block|Size" FORMAT 99,999
|
|
COLUMN offline_disks HEAD "Offline|Disks"
|
|
COLUMN voting_files HEAD "Voting|Files" FORMAT a6
|
|
COLUMN TOTAL_GB HEAD "(A)||Total|Avail|(&size_label)"
|
|
COLUMN used_GB HEAD "(B)||Total|Used|(&size_label)"
|
|
COLUMN hot_used_GB HEAD "Hot|Used|(&size_label)"
|
|
COLUMN cold_used_GB HEAD "Cold|Used|(&size_label)"
|
|
COLUMN FREE_GB HEAD "(C)||Total|Free|(&size_label)"
|
|
COLUMN REQUIRED_MIRROR_FREE_GB HEAD "(D)|Required|Free|ForMirror|(&size_label)" JUSTIFY RIGHT
|
|
COLUMN mirrored_total_GB HEAD "(E)||Mirrored|Avail|(&size_label)"
|
|
COLUMN mirrored_used_GB HEAD "(F)||Mirrored|Used|(&size_label)"
|
|
COLUMN mirrored_free_GB HEAD "Mirrored|Free|(&size_label)"
|
|
COLUMN USABLE_GB HEAD "(C-D)/REDUN||Usable|Free|(&size_label)"
|
|
|
|
BREAK ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF TOTAL_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF USED_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF HOT_USED_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF FREE_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF MIRRORED_TOTAL_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF MIRRORED_USED_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF MIRRORED_FREE_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT
|
|
|
|
SELECT d.NAME diskgroup_name
|
|
, d.group_number
|
|
, d.state
|
|
, d.type
|
|
, d.compatibility
|
|
, d.database_compatibility
|
|
, d.allocation_unit_size/1024/1024 allocation_unit_size_MB
|
|
, d.sector_size
|
|
, d.block_size
|
|
, d.offline_disks
|
|
, d.voting_files
|
|
FROM v$asm_diskgroup_stat d;
|
|
|
|
PROMPT
|
|
PROMPT ******************************************************
|
|
PROMPT * A S M D I S K G R O U P A T T R I B U T E S *
|
|
PROMPT ******************************************************
|
|
|
|
COLUMN attribute HEAD "Attribute" FORMAT A25
|
|
COLUMN system_created HEAD "System|Created" FORMAT A10
|
|
COLUMN read_only HEAD "Read|Only" FORMAT A5
|
|
COLUMN value HEAD "Value" FORMAT A15
|
|
|
|
SELECT d.name diskgroup_name
|
|
, a.name attribute
|
|
, DECODE(a.system_created,'N','No','Y','Yes',a.read_only) system_created
|
|
, DECODE(a.read_only,'N','No','Y','Yes',a.read_only) read_only
|
|
, a.value
|
|
FROM v$asm_diskgroup_stat d
|
|
, v$asm_attribute a
|
|
WHERE a.group_number = d.group_number
|
|
AND a.name not like 'template%'
|
|
ORDER BY attribute
|
|
, diskgroup_name
|
|
;
|
|
|
|
|
|
@@footer
|