I am looking to write a query on a management view. We need to be able to report on how much space is used on a disk group.
for example our Archives are stored on FRA disk group, lets say there are 20 different clusters that all have a disk group called FRA.
1) current and historical used disk space / free disk space
2) which files ( with size) are on these disk groups.
Does anyone have experience with this.
I am not sure if this type of detail is stored in sysman tables, but you can certainly query ASM itself for the data. Check out this script:
Script to report the list of files stored in ASM and CURRENTLY NOT OPENED [ID 552082.1]
I use a form of the script to detail consumption by database within disk group on ASM 11.2 and later. Play around with the sys_connect_by_path function. Depending on how you store content in ASM, you may be able to extract the database name with the function.
Getting historical consumption from EM11g may be more of a challenge especially at any level of detail. You may consider storing summary content yourself for reporting. I create a small DBA-only database and send ASM detail to it.