Monitor ASM disk groups

----------------------------------------------------
-- Diskgroups are having less than 20% free space --
----------------------------------------------------
CLEAR COLUMNS
DEFINE LINE2='                               CRITICAL!!! Diskgroups are having less than 20% free space                               '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT NAME DISKGROUP_NAME, 
FREE_MB, 
TOTAL_MB, 
ROUND((FREE_MB/TOTAL_MB)*100, 2) "% FREE" 
FROM V$ASM_DISKGROUP
WHERE ROUND((FREE_MB/TOTAL_MB)*100, 2) < 20;

----------------------------------------------------------
-- Application Tablespaces those are more than 80% full --
----------------------------------------------------------
CLEAR COLUMNS
COL TABLESPACE_NAME FORMAT A35
COL "USED SPACE" FOR 999999999
COL "TOTAL SPACE" FOR 999999999
COL "FREE SPACE" FOR 999999999
DEFINE LINE2='                 CRITICAL!!! Application Tablespaces those are more than 80% full (All sizes are in MB)                 '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT TABLESPACE_NAME,
ROUND(SUM(TOTAL_MB),2) "TOTAL SPACE",
ROUND(SUM(TOTAL_MB)-SUM(FREE_MB),2) "USED SPACE",
ROUND(SUM(MAX_MB)-(SUM(TOTAL_MB)-SUM(FREE_MB)),2) "FREE SPACE",
ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(MAX_MB)*100) "OVERALL % FULL"
FROM (
SELECT TABLESPACE_NAME,
SUM(BYTES)/1024/1024 FREE_MB,
0 TOTAL_MB,
0 MAX_MB 
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME
UNION 
SELECT TABLESPACE_NAME,
0 CURRENT_MB,
SUM(BYTES)/1024/1024 TOTAL_MB,
SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/1024/1024 MAX_MB
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME
) 
GROUP BY TABLESPACE_NAME
HAVING ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(MAX_MB)*100) > 80;

------------------------------------------------------
-- Temporary Tablespace which is more than 80% full --
------------------------------------------------------
CLEAR COLUMNS
COL TABLESPACE FORMAT A20
COL "USED SPACE" FOR 999999999
COL "TOTAL SPACE" FOR 999999999
COL "FREE SPACE" FOR 999999999
DEFINE LINE2='                   CRITICAL!!! Temporary Tablespace which is more than 80% full (All sizes are in MB)                   '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT A.INST_ID, 
A.TABLESPACE_NAME TABLESPACE, 
D.MB_TOTAL "TOTAL SPACE",
SUM(A.USED_BLOCKS * D.BLOCK_SIZE)/1024/1024 "USED SPACE",
D.MB_TOTAL-SUM (A.USED_BLOCKS * D.BLOCK_SIZE)/1024/1024 "FREE SPACE",
ROUND((SUM(A.USED_BLOCKS * D.BLOCK_SIZE)/1024/1024/D.MB_TOTAL)*100,2) "OVERALL % FULL"
FROM GV$SORT_SEGMENT A, (
SELECT B.INST_ID,
B.NAME, 
C.BLOCK_SIZE, 
SUM(C.BYTES)/1024/1024 MB_TOTAL
FROM GV$TABLESPACE B, GV$TEMPFILE C
WHERE B.TS# = C.TS# 
AND C.INST_ID = B.INST_ID
GROUP BY B.INST_ID,B.NAME, C.BLOCK_SIZE
) D
WHERE A.TABLESPACE_NAME = D.NAME 
AND A.INST_ID = D.INST_ID
GROUP BY A.INST_ID, A.TABLESPACE_NAME, D.MB_TOTAL
HAVING ROUND((SUM(A.USED_BLOCKS * D.BLOCK_SIZE)/1024/1024/D.MB_TOTAL)*100,2) > 80;