140 lines
8.0 KiB
SQL
140 lines
8.0 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : ASM DiskGroup Sizes and Space Usage
|
|
* Parameter : NONE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-JUN-13 Vishal Gupta Changed output layout, also switch from v$asm_diskgroup
|
|
* to v$asm_diskgroup_stat view to avoid diskgroup discovery
|
|
* 14-Dec-12 Vishal Gupta Added Usable %age
|
|
* 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 * A S M D I S K G R O U P S P A C E U S A G E R E P O R T *
|
|
PROMPT **********************************************************************
|
|
|
|
|
|
COLUMN 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 offline_disks HEAD "Offline|Disks"
|
|
|
|
COLUMN separator HEAD "!|!|!|!|!|!" FORMAT a1
|
|
|
|
COLUMN mirrored_total_GB HEAD "(E)||Mirrored|Total|(&size_label)"
|
|
COLUMN USABLE_TOTAL_GB HEAD "(A-D)|/REDUN||Safely|Usable|Total|(&size_label)"
|
|
COLUMN USABLE_USED_GB HEAD "(B-D)|/REDUN||Safely|Usable|Used|(&size_label)"
|
|
COLUMN USABLE_FREE_GB HEAD "(C-D)|/REDUN||Safely|Usable|Free|(&size_label)"
|
|
COLUMN USABLE_USED_PERCENT HEAD "Safely|Usable|Used|(%)" FORMAT 999.99
|
|
COLUMN USABLE_FREE_PERCENT HEAD "Safely|Usable|Free|(%)" FORMAT 999.99
|
|
COLUMN mirrored_free_GB HEAD "Mirrored|Free|(&size_label)"
|
|
|
|
|
|
COLUMN REQUIRED_MIRROR_FREE_GB HEAD "(D)|Required|Free|ForMirror|(&size_label)" JUSTIFY RIGHT
|
|
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)"
|
|
|
|
|
|
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
|
|
COMPUTE SUM LABEL 'Total' OF USABLE_TOTAL_GB FORMAT 9,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF USABLE_USED_GB FORMAT 9,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF USABLE_FREE_GB FORMAT 9,999,999 ON REPORT
|
|
|
|
|
|
/*
|
|
|
|
SAMPLE OUTPUT
|
|
|
|
**********************************************************************
|
|
* A S M D I S K G R O U P S P A C E U S A G E R E P O R T *
|
|
**********************************************************************
|
|
|
|
. <----------------------- With Mirroring ----------------------> <-------------------- Without Mirroring ---------------->
|
|
|
|
(A-D) (B-D) (C-D)
|
|
! /REDUN /REDUN /REDUN ! !
|
|
! (E) ! (D) (A) (B) (C) !
|
|
! Safely Safely Safely Safely Safely ! Required !
|
|
! Mirrored Usable Usable Usable Mirrored Usable Usable ! Free Total Total Hot Cold Total !
|
|
Redund ! Avail Total Used Free Free Used Free ! ForMirror Avail Used Used Used Free !
|
|
DiskGroup G# Type ! (GB) (GB) (GB) (GB) (GB) (%) (%) ! (GB) (GB) (GB) (GB) (GB) (GB) !
|
|
------------ --- ------ - -------- -------- -------- -------- -------- ------- ------- - --------- -------- -------- -------- -------- -------- -
|
|
DATA_DM01 1 NORMAL ! 92,736 86,112 44,488 41,624 48,248 51.66 48.34 ! 13,248 185,472 88,977 0 88,977 96,495 !
|
|
DATA 2 NORMAL ! 18,480 17,160 8,145 9,015 10,335 47.47 52.53 ! 2,640 36,960 16,291 0 16,291 20,669 !
|
|
DBFS_DG 3 NORMAL ! 2,366 2,197 2 2,195 2,364 .07 99.93 ! 338 4,732 3 0 3 4,728 !
|
|
FLASH_DM01 4 NORMAL ! 2,240 2,080 892 1,188 1,348 42.88 57.12 ! 320 4,480 1,784 0 1,784 2,696 !
|
|
RECO_DM01 5 NORMAL ! 139,113 129,177 38,516 90,660 100,597 29.82 70.18 ! 19,873 278,226 77,033 0 77,033 201,194 !
|
|
RECO 6 NORMAL ! 25,929 24,077 21,893 2,184 4,036 90.93 9.07 ! 3,704 51,857 43,786 0 43,786 8,071 !
|
|
SYSTEMDG 7 NORMAL ! 2,039 1,893 2 1,891 2,036 .11 99.89 ! 291 4,077 4 0 4 4,073 !
|
|
-------- -------- -------- -------- -------- --------- -------- -------- -------- -------- --------
|
|
Total 282,903 262,696 113,938 148,757 168,964 40,414 565,804 227,878 0 227,878 337,926
|
|
|
|
|
|
|
|
*/
|
|
|
|
|
|
PROMPT
|
|
PROMPT . <----------------------- With Mirroring ----------------------> <-------------------- Without Mirroring ---------------->
|
|
|
|
-- Note:
|
|
-- The GROUP_NUMBER, TOTAL_MB, and FREE_MB columns are only
|
|
-- meaningful if the disk group is mounted by the instance. Otherwise, their values will be 0.
|
|
|
|
SELECT NAME
|
|
, group_number
|
|
, type
|
|
, '!' separator
|
|
, ROUND(TOTAL_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) mirrored_TOTAL_GB
|
|
, ROUND((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) USABLE_TOTAL_GB
|
|
, ROUND((hot_used_mb + cold_used_mb)/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3) /1024) USABLE_USED_GB
|
|
, ROUND(USABLE_FILE_MB /1024) USABLE_FREE_GB
|
|
, ROUND((USABLE_FILE_MB + ROUND(REQUIRED_MIRROR_FREE_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) ) /1024) mirrored_free_GB
|
|
, ROUND( (hot_used_mb + cold_used_mb) / (TOTAL_MB - REQUIRED_MIRROR_FREE_MB ) * 100 , 2) USABLE_USED_PERCENT
|
|
, ROUND(USABLE_FILE_MB /((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) * 100 ,2) USABLE_FREE_PERCENT
|
|
, '!' separator
|
|
, ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB
|
|
, ROUND(TOTAL_MB/1024) TOTAL_GB
|
|
, ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB
|
|
, ROUND(hot_used_mb/1024) HOT_USED_GB
|
|
, ROUND(cold_used_mb/1024) COLD_USED_GB
|
|
, ROUND(FREE_MB/1024) FREE_GB
|
|
, '!' separator
|
|
FROM v$asm_diskgroup_stat;
|
|
|
|
|
|
@@footer
|