@@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