113 lines
4.4 KiB
MySQL
113 lines
4.4 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display ASM diskgroup usage by directory
|
|
* Parameters : 1 - DiskGroup (% - wildchar, \ - escape char, default is '%')
|
|
* 2 - Directory (% - wildchar, \ - escape char, default is '%')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-Apr-12 Vishal Gupta First Draft
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE DISKGROUP_NAME
|
|
UNDEFINE DISK_NAME
|
|
|
|
DEFINE DISKGROUP_NAME="&&1"
|
|
DEFINE DIRECTORY="&&2"
|
|
|
|
set term off
|
|
COLUMN _DISKGROUP_NAME NEW_VALUE DISKGROUP_NAME NOPRINT
|
|
COLUMN _DIRECTORY NEW_VALUE DIRECTORY NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&DISKGROUP_NAME','','%','&&DISKGROUP_NAME')) "_DISKGROUP_NAME"
|
|
, UPPER(DECODE('&&DIRECTORY','','%','&&DIRECTORY')) "_DIRECTORY"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT
|
|
PROMPT **********************************************************************
|
|
PROMPT * A S M D I S K G R O U P S P A C E U S A G E *
|
|
PROMPT * (By FileType and Directory) *
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - DiskGroup Name = '&&DISKGROUP_NAME'
|
|
PROMPT * - Directory = '&&DIRECTORY'
|
|
PROMPT **********************************************************************
|
|
|
|
COLUMN DiskGroup HEADING "DiskGroup" FORMAT a20
|
|
COLUMN file_type HEADING "FileType" FORMAT a20
|
|
COLUMN Mirrored_UsedSize HEADING "Mirrored|UsedSize|(GB)" FORMAT 999,999,999
|
|
COLUMN UnMirrored_UsedSize HEADING "UnMirrored|UsedSize|(GB)" FORMAT 999,999,999
|
|
COLUMN parent_directory HEADING "Directory" FORMAT a60
|
|
|
|
|
|
WITH asm_alias AS (SELECT /*+ NO_MERGE MATERIALIZE */ * FROM v$asm_alias )
|
|
, asm_diskgroup_stat AS (SELECT /*+ NO_MERGE MATERIALIZE */ * FROM v$asm_diskgroup_stat )
|
|
, asm_file AS (SELECT /*+ NO_MERGE MATERIALIZE */ * FROM v$asm_file )
|
|
, aliases as (SELECT /*+ no_merge materialize */
|
|
SYS_CONNECT_BY_PATH(a.name, '/') File_Path
|
|
, level hierarchy_level
|
|
, CONNECT_BY_ISLEAF
|
|
, a.*
|
|
FROM asm_alias a
|
|
CONNECT BY NOCYCLE
|
|
PRIOR a.group_number = a.group_number
|
|
AND PRIOR a.reference_index = a.parent_index
|
|
START WITH mod(parent_index, power(2, 24)) = 0
|
|
AND reference_index IN (SELECT a2.reference_index
|
|
FROM asm_alias a2
|
|
WHERE (mod(a2.parent_index, power(2, 24))) = 0
|
|
/*Parent level database directory e.g. +DATA/MYDB etc */
|
|
)
|
|
)
|
|
, files as
|
|
(
|
|
SELECT SUBSTR('+' || g.name || a.file_path,1, INSTR('+' || g.name || a.file_path,'/',-1) ) as parent_directory
|
|
, '+' || g.name || a.file_path as file_path
|
|
, g.name DiskGroup
|
|
, f.type
|
|
, f.block_size
|
|
, f.space
|
|
, f.redundancy
|
|
, f.redundancy_lowered
|
|
, f.striped
|
|
, f.bytes
|
|
, f.creation_date
|
|
, f.modification_date
|
|
FROM asm_diskgroup_stat g
|
|
JOIN aliases a ON a.group_number = g.group_number
|
|
LEFT OUTER JOIN asm_file f ON f.group_number = a.group_number
|
|
AND f.file_number = a.file_number
|
|
AND f.incarnation = a.file_incarnation
|
|
)
|
|
SELECT af.DiskGroup
|
|
, af.type file_type
|
|
, ROUND(sum(af.bytes)/1024/1024/1024) Mirrored_UsedSize
|
|
, ROUND(sum(af.space)/1024/1024/1024) UnMirrored_UsedSize
|
|
, af.parent_directory
|
|
FROM files af
|
|
WHERE af.diskgroup like '&&DISKGROUP_NAME' ESCAPE '\'
|
|
AND af.file_path LIKE '&&DIRECTORY' ESCAPE '\'
|
|
GROUP BY af.DiskGroup
|
|
, af.type
|
|
, af.parent_directory
|
|
ORDER BY af.DiskGroup
|
|
, af.type
|
|
, af.parent_directory
|
|
;
|
|
|
|
|
|
@@footer
|