Last active
April 26, 2019 06:03
-
-
Save fljdin/53b10ed3e0e55ad91c23 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set lines 255 | |
col path for a35 | |
col Diskgroup for a15 | |
col DiskName for a25 | |
col disk# for 999 | |
col total_mb for 999,999,999 | |
col free_mb for 999,999,999 | |
compute sum of total_mb on DiskGroup | |
compute sum of free_mb on DiskGroup | |
break on DiskGroup skip 1 on report - | |
set pages 255 | |
select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, | |
b.total_mb, b.free_mb, | |
-- b.path, | |
b.header_status | |
from v$asm_disk b, v$asm_diskgroup a | |
where a.group_number (+) =b.group_number | |
order by b.group_number, b.disk_number, b.name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set lines 120 | |
col "Redundancy" for a15 | |
col "Diskgroup" for a12 | |
select a.name "Diskgroup" , round(sum(b.total_mb)/1024,1) "Size GB", | |
round(sum(b.free_mb)/1024,1) "Free GB", | |
decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024,1),'NORMAL',round(sum(b.free_mb)/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/3,1)) "Usable GB", | |
decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024/1024,1),'NORMAL',round(sum(b.free_mb)/1024/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/1024/3,1)) "Usable TB", | |
round((sum(b.total_mb)-sum(b.free_mb))/sum(b.total_mb)*1000)/10 "Use%", | |
a.type "Redundancy" | |
from v$asm_disk b, v$asm_diskgroup a | |
where a.group_number (+) =b.group_number | |
group by a.name, a.type order by 1; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set pages 100 | |
col database for a15 | |
SELECT NVL(dbname, '-- TOTAL') database, round(SUM(space)/1024/1024) mb_used, | |
round(SUM(space) / AVG(total_mb * 1024 * 1024) * 100, 2) pct_used | |
FROM ( | |
SELECT gname, file_type, space, aname, system_created, alias_directory, | |
regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, total_mb | |
FROM ( | |
SELECT system_created, alias_directory, file_type,space, level, gname, aname, | |
concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, total_mb | |
FROM ( | |
SELECT b.name gname, b.total_mb, a.parent_index pindex, a.name aname, | |
a.reference_index rindex, a.system_created, a.alias_directory, | |
c.type file_type, c.space | |
FROM v$asm_alias a | |
JOIN v$asm_diskgroup b ON a.group_number = b.group_number | |
LEFT JOIN v$asm_file c ON a.group_number = c.group_number | |
AND a.file_number = c.file_number | |
AND a.file_incarnation = c.incarnation | |
) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN ( | |
SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b | |
WHERE a.group_number = b.group_number | |
AND (mod(a.parent_index, power(2, 24))) = 0 | |
) CONNECT BY prior rindex = pindex | |
) WHERE NOT file_type IS NULL AND system_created = 'Y' ) | |
GROUP BY ROLLUP (dbname) | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select name, total_mb, free_mb, | |
round(100*(total_mb-free_mb)/greatest(1,total_mb),0) as used_pct | |
from v$asm_diskgroup; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment