The query below can be used in order to recognize overbooked ASM diskgroups.
The value of column real_avail_gb should be >= 0
-----------------------------------------------------------------------
-- total_gb : Physical size of the disk group in GB
-- usable_gb : Corresponds to usable_file_mb in GB
-- i.e. Amount of free space that can be safely
-- utilized
-- taking mirroring into account and yet be able to
-- restore redundancy after a disk failure
-- df_maxsizes_gb: Sum of the datafiles max sizes in GB
-- real_avail_gb : (total_gb - real_avail_gb)
-- Shows if there would be enough space on the
-- diskgroup even if all files would grow up to their
-- maxsize
-----------------------------------------------------------------------
SELECT name, total_gb, usable_gb, df_maxsizes_gb,
(total_gb - df_maxsizes_gb) real_avail_gb
FROM
(
SELECT name, total_mb/1024 total_GB,
-- free_mb/1024 free_gb,
usable_file_mb/1024 usable_gb,
DF.maxsize_gb df_maxsizes_gb
FROM v$asm_diskgroup,
(
SELECT SUBSTR(file_name, 2,
INSTR(file_name, '/', 1) - 2) disk_group,
SUM(DECODE(maxbytes,0,bytes/(1024*1024*1024),
maxbytes/(1024*1024*1024))
) maxsize_gb
FROM sys.dba_data_files
GROUP BY SUBSTR(file_name, 2, INSTR(file_name, '/', 1) - 2)
) DF
WHERE DF.disk_group = name
)
ORDER BY 1;
Example output:
NAME
|
TOTAL_GB
|
USABLE_GB
|
DF_MAXSIZES_GB
|
REAL_AVAIL_GB
|
U01
|
1600
|
236.484375
|
1504.064453125
|
95.935546875
|
The query from above was built based on the following queries
-----------------------------------------------------------------------
-- Quering the ASM diskgroups directly from the DB
-- (no need to logon to the ASM DB instance)
-----------------------------------------------------------------------
SELECT name,
total_mb/1024 total_GB,
free_mb/1024 free_gb,
usable_file_mb/1024 usable_gb
FROM v$asm_diskgroup;
-----------------------------------------------------------------------
-- Tablespace MaxSizes by ASM diskgroup
-- Precondition: Diskgroup starts with +.
-- For example: file_name="+U01/folder/file.ext" => disk_group="U01"
-----------------------------------------------------------------------
SELECT SUBSTR(file_name, 2, INSTR(file_name, '/', 1) - 2) disk_group,
tablespace_name,
SUM(DECODE(maxbytes,0,bytes/(1024*1024*1024),
maxbytes/(1024*1024*1024))
) maxsize_gb
FROM sys.dba_data_files
GROUP BY SUBSTR(file_name, 2, INSTR(file_name, '/', 1) - 2), tablespace_name
ORDER BY 1;
