REM
/*
 * ASMFreeSpace.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
PROMPT ErrorThreholdPCT represents the precentage of free disk space below which an error condition should be noted.
DEF ErrorThresholdPct=&&1
SET ECHO off VER off FEEDBACK off LINES 128

COLUMN DGName  FORMAT A32 HEADING "ASM Disk Group"
COLUMN TotalMB FORMAT 9,999,999,999 HEADING "Total Space (MB)"
COLUMN UsedMB  FORMAT 9,999,999,999 HEADING "Used Space (MB)"
COLUMN FreeMB  FORMAT 9,999,999,999 HEADING "|Free Space (MB)"
COLUMN UsableMB  FORMAT 9,999,999,999 HEADING "Usable|Free Space (MB)"
COLUMN UsedPct FORMAT 999.99 HEADING "Used%"
COLUMN FreePct FORMAT 999.99 HEADING "Free%"
COLUMN UsablePct FORMAT 999.99 HEADING "Usable|Free%"
COLUMN Msg FORMAT A5 HEADING "State"
SELECT name DGName
	,total_mb TotalMB
	,total_mb-free_mb UsedMB
	,free_mb FreeMb
	,usable_file_mb UsableMB
	,ROUND(((total_mb-free_mb)/total_mb)*100,2) UsedPct
	,ROUND((free_mb/total_mb)*100,2) FreePct
	,ROUND((usable_file_mb/total_mb)*100,2) UsablePct
	,CASE WHEN CEIL((usable_file_mb/total_mb)*100) < &&ErrorThresholdPct THEN 'ERROR' ELSE 'OK' END Msg
FROM v$asm_diskgroup
ORDER BY 8 ASC;
REM