218 lines
13 KiB
MySQL
218 lines
13 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : ASM usage by file type for all mounted diskgroups (including other databases)
|
|
* Parameters : 1 - DBName (% - wildchar, \ - escape char, default is '%')
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------------------------------------
|
|
* 24-Dec-14 Vishal Gupta Enhanced script to display file only in alias directory if alias is present
|
|
* 21-Jan-14 Vishal Gupta Commented out unmirrored figures from output
|
|
* 18-Jul-13 Vishal Gupta Created
|
|
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE DBNAME
|
|
|
|
DEFINE DBNAME="&&1"
|
|
|
|
set term off
|
|
COLUMN _DBNAME NEW_VALUE DBNAME NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&DBNAME','','%',UPPER('&&DBNAME'))) "_DBNAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT *******************************************************************
|
|
PROMPT * A S M S P A C E U S A G E B Y D A T A B A S E *
|
|
PROMPT * *
|
|
PROMPT * - For all diskgroups mounted in current instance *
|
|
PROMPT * - Including files for other databases *
|
|
PROMPT * *
|
|
PROMPT * Input Parameters *
|
|
PROMPT * - DB Name = '&&DBNAME'
|
|
PROMPT *******************************************************************
|
|
|
|
DEFINE size_label=GB
|
|
DEFINE size_divider="1024/1024/1024"
|
|
|
|
|
|
COLUMN diskgroup_name HEADING "DiskGroup" FORMAT A16
|
|
COLUMN DBNAME HEADING "DBName" FORMAT A15
|
|
|
|
COLUMN DISKGROUP_MirroredSize HEADING "Total|(&&size_label)" FORMAT 999,999
|
|
COLUMN DATAFILE_MirroredSize HEADING "Data|File|(&&size_label)" FORMAT 999,999
|
|
COLUMN TEMPFILE_MirroredSize HEADING "Temp|File|(&&size_label)" FORMAT 999,999
|
|
COLUMN ONLINELOG_MirroredSize HEADING "Redo|Log|(&&size_label)" FORMAT 99,999
|
|
COLUMN ARCHIVELOG_MirroredSize HEADING "Arch|Log|(&&size_label)" FORMAT 999,999
|
|
COLUMN BACKUPSET_MirroredSize HEADING "Backup|Set|(&&size_label)" FORMAT 999,999
|
|
COLUMN FLASHBACK_MirroredSize HEADING "Flash|Back|(&&size_label)" FORMAT 999,999
|
|
COLUMN OCRFILE_MirroredSize HEADING "OCR|(&&size_label)" FORMAT 999
|
|
COLUMN AUTOBACKUP_MirroredSize HEADING "Auto|Backup|(&&size_label)" FORMAT 999,999
|
|
COLUMN CONTROLFILE_MirroredSize HEADING "Ctrl|File|(&&size_label)" FORMAT 999
|
|
COLUMN PARAMFILE_MirroredSize HEADING "Param|File|(&&size_label)" FORMAT 999
|
|
|
|
COLUMN DISKGROUP_UnMirroredSize HEADING "Total|(&&size_label)" FORMAT 999,999
|
|
COLUMN DATAFILE_UnMirroredSize HEADING "Data|File|(&&size_label)" FORMAT 999,999
|
|
COLUMN TEMPFILE_UnMirroredSize HEADING "Temp|File|(&&size_label)" FORMAT 999,999
|
|
COLUMN ONLINELOG_UnMirroredSize HEADING "Redo|Log|(&&size_label)" FORMAT 9,999
|
|
COLUMN ARCHIVELOG_UnMirroredSize HEADING "Arch|Log|(&&size_label)" FORMAT 999,999
|
|
COLUMN BACKUPSET_UnMirroredSize HEADING "Backup|Set|(&&size_label)" FORMAT 999,999
|
|
COLUMN FLASHBACK_UnMirroredSize HEADING "Flash|Back|(&&size_label)" FORMAT 999,999
|
|
COLUMN OCRFILE_UnMirroredSize HEADING "OCR|(&&size_label)" FORMAT 999
|
|
COLUMN AUTOBACKUP_UnMirroredSize HEADING "Auto|Backup|(&&size_label)" FORMAT 999,999
|
|
COLUMN CONTROLFILE_UnMirroredSize HEADING "Ctrl|File|(&&size_label)" FORMAT 999
|
|
COLUMN PARAMFILE_UnMirroredSize HEADING "Param|File|(&&size_label)" FORMAT 999
|
|
|
|
BREAK ON REPORT
|
|
|
|
/*
|
|
BREAK ON diskgroup_name SKIP 1 DUPLICATES ON REPORT
|
|
|
|
COMPUTE SUM LABEL 'Total' OF DISKGROUP_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF DATAFILE_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF TEMPFILE_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF ONLINELOG_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF ARCHIVELOG_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF BACKUPSET_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF FLASHBACK_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF OCRFILE_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF AUTOBACKUP_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF CONTROLFILE_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
COMPUTE SUM LABEL 'Total' OF PARAMFILE_MirroredSize FORMAT 99,999,999 ON diskgroup_name
|
|
*/
|
|
|
|
COMPUTE SUM LABEL 'Total' OF DISKGROUP_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF DATAFILE_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF TEMPFILE_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF ONLINELOG_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF ARCHIVELOG_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF BACKUPSET_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF FLASHBACK_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF OCRFILE_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF AUTOBACKUP_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF CONTROLFILE_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF PARAMFILE_MirroredSize FORMAT 99,999,999 ON REPORT
|
|
|
|
COMPUTE SUM LABEL 'Total' OF DISKGROUP_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF DATAFILE_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF TEMPFILE_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF ONLINELOG_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF ARCHIVELOG_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF BACKUPSET_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF FLASHBACK_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF OCRFILE_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF AUTOBACKUP_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF CONTROLFILE_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF PARAMFILE_UnMirroredSize FORMAT 99,999,999 ON REPORT
|
|
|
|
|
|
/*
|
|
PROMPT
|
|
PROMPT
|
|
PROMPT - <------------------------ Mirrored (GB) --------------------------------> <------------------------- UnMirrored (GB) ----------------------------->
|
|
*/
|
|
|
|
|
|
WITH asm_alias AS (SELECT /*+ NO_MERGE MATERIALIZE */ * FROM v$asm_alias )
|
|
, asm_diskgroup_stat AS (SELECT /*+ NO_MERGE */ * FROM v$asm_diskgroup )
|
|
, asm_file AS (SELECT /*+ NO_MERGE MATERIALIZE */ * FROM v$asm_file )
|
|
, files AS (SELECT /*+ NO_MERGE MATERIALIZE */
|
|
diskgroup_name
|
|
, SUBSTR(SYS_CONNECT_BY_PATH(alias_name, '/'),2) File_Path
|
|
, alias_name
|
|
, type
|
|
, alias_directory
|
|
, space
|
|
, bytes
|
|
, system_created
|
|
, file_number
|
|
, incarnation
|
|
FROM (SELECT dg.name diskgroup_name
|
|
, a.parent_index parent_index
|
|
, a.name alias_name
|
|
, a.reference_index reference_index
|
|
, a.system_created
|
|
, a.alias_directory
|
|
, f.type
|
|
, f.space
|
|
, f.bytes
|
|
, f.file_number
|
|
, f.incarnation
|
|
FROM asm_diskgroup_stat dg
|
|
JOIN asm_alias a ON a.group_number = dg.group_number
|
|
/* Outer join because alias could be a directory */
|
|
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
|
|
-- WHERE dg.name LIKE '&&DISKGROUP_NAME'
|
|
)
|
|
CONNECT BY NOCYCLE PRIOR reference_index = parent_index
|
|
START WITH mod(parent_index, power(2, 24)) = 0 /*Parent level database directory e.g. +DATA/MYDB etc */
|
|
AND alias_name LIKE '&&DBNAME'
|
|
|
|
/*
|
|
AND reference_index IN (SELECT a2.reference_index
|
|
FROM asm_alias a2
|
|
WHERE (mod(a2.parent_index, power(2, 24))) = 0
|
|
)
|
|
*/
|
|
)
|
|
|
|
SELECT UPPER(SUBSTR(NVL(f2.File_Path,f.file_path),1,instr(NVL(f2.File_Path,f.file_path),'/')-1)) dbname
|
|
, ROUND(SUM(f.bytes)/&&size_divider) DISKGROUP_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'DATAFILE',f.bytes,0))/&&size_divider) DATAFILE_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'TEMPFILE',f.bytes,0))/&&size_divider) TEMPFILE_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'ONLINELOG',f.bytes,0))/&&size_divider) ONLINELOG_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'ARCHIVELOG',f.bytes,0))/&&size_divider) ARCHIVELOG_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'BACKUPSET',f.bytes,0))/&&size_divider) BACKUPSET_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'FLASHBACK',f.bytes,0))/&&size_divider) FLASHBACK_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'OCRFILE',f.bytes,0))/&&size_divider) OCRFILE_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'AUTOBACKUP',f.bytes,0))/&&size_divider) AUTOBACKUP_MirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'CONTROLFILE',f.bytes,0))/&&size_divider) CONTROLFILE_MirroredSize
|
|
--, ROUND(SUM(DECODE(f.type, 'PARAMETERFILE',f.bytes,'ASMPARAMETERFILE',f.bytes,0))/&&size_divider) PARAMFILE_MirroredSize
|
|
/*
|
|
, ROUND(SUM(f.space)/&&size_divider) DISKGROUP_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'DATAFILE',f.space,0))/&&size_divider) DATAFILE_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'TEMPFILE',f.space,0))/&&size_divider) TEMPFILE_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'ONLINELOG',f.space,0))/&&size_divider) ONLINELOG_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'ARCHIVELOG',f.space,0))/&&size_divider) ARCHIVELOG_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'BACKUPSET',f.space,0))/&&size_divider) BACKUPSET_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'FLASHBACK',f.space,0))/&&size_divider) FLASHBACK_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'OCRFILE',f.space,0))/&&size_divider) OCRFILE_UnMirroredSize
|
|
, ROUND(SUM(DECODE(f.type, 'AUTOBACKUP',f.space,0))/&&size_divider) AUTOBACKUP_UnMirroredSize
|
|
--, ROUND(SUM(DECODE(f.type, 'CONTROLFILE',f.space,0))/&&size_divider) CONTROLFILE_UnMirroredSize
|
|
--, ROUND(SUM(DECODE(f.type, 'PARAMETERFILE',f.space,'ASMPARAMETERFILE',f.space,0))/&&size_divider) PARAMFILE_UnMirroredSize
|
|
*/
|
|
FROM files f /* System created files */
|
|
LEFT OUTER JOIN files f2 /* File alias */
|
|
ON f2.diskgroup_name = f.diskgroup_name
|
|
AND f2.file_number = f.file_number
|
|
AND f2.incarnation = f.incarnation
|
|
AND f2.system_created = 'N'
|
|
AND f2.alias_directory = 'N'
|
|
AND f.system_created = 'Y'
|
|
AND f.alias_directory = 'N'
|
|
WHERE 1=1
|
|
AND f.system_created = 'Y' /* Dont remove this condition */
|
|
AND f.alias_directory = 'N'
|
|
AND UPPER(SUBSTR(NVL(f2.File_Path,f.file_path),1,instr(NVL(f2.File_Path,f.file_path),'/')-1)) LIKE '&&DBNAME' ESCAPE '\'
|
|
GROUP BY UPPER(SUBSTR(NVL(f2.File_Path,f.file_path),1,INSTR(NVL(f2.File_Path,f.file_path),'/')-1)) /*dbname*/
|
|
ORDER BY dbname
|
|
;
|
|
|
|
|
|
@@footer
|