@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display ASM diskgroup usage by Filetype for current database.
*  Parameters : NONE
*                                   
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  15-Mar-12  Vishal Gupta  First Draft
*/

set lines 200
COLUMN FileType FORMAT a20
COLUMN DiskGroup FORMAT a20
COLUMN SizeGB    FORMAT 999,999,999.99

BREAK ON REPORT

COMPUTE SUM  LABEL "Total" OF SizeGB  FORMAT 99,999,999,999.99 ON REPORT

SelECt Diskgroup, FileType , SizeGB
FROM
(
select 'DATAFILE' FileType, SUBSTR(name,1, instr(name,'/') -1) DISKGROUP, ROUND(sum(bytes)/1024/1024/1024,2)  SizeGB
from v$datafile 
group by SUBSTR(name,1, instr(name,'/') -1) 
UNION ALL
select 'TEMPFILE' FileType, SUBSTR(name,1, instr(name,'/') -1) DISKGROUP, ROUND(sum(bytes)/1024/1024/1024,2)  
from v$tempfile 
group by SUBSTR(name,1, instr(name,'/') -1) 
UNION ALL
select 'ONLINE REDO' FileType, SUBSTR(member,1, instr(member,'/') -1) DISKGROUP, ROUND(sum(bytes)/1024/1024/1024,2)  
from v$log l , v$logfile lf 
where l.group# = lf.group# 
group by SUBSTR(member,1, instr(member,'/') -1) 
UNION ALL
select 'ARCHIVE LOG' FileType, SUBSTR(name,1, instr(name,'/') -1) DISKGROUP, ROUND(sum(blocks* block_size)/1024/1024/1024,2)  
from v$archived_log 
where dest_id = 1 and DELETED = 'NO' and status = 'A'
group by SUBSTR(name,1, instr(name,'/') -1) 
UNION ALL
SELECT 'CONTROLFILE' FileType, SUBSTR(name,1, instr(name,'/') -1) DISKGROUP, ROUND(sum(file_size_blks* block_size)/1024/1024/1024,2)  
from v$controlfile 
group by SUBSTR(name,1, instr(name,'/') -1) 
UNION ALL
select 'BCT_FILE' FileType, SUBSTR(filename,1, instr(filename,'/') -1) DISKGROUP, ROUND(sum(bytes)/1024/1024/1024,2) 
from v$block_change_tracking where status  = 'ENABLED'
group by  SUBSTR(filename,1, instr(filename,'/') -1)
ORDER BY FileType, DISKGROUP
)
ORDER BY DiskGroup,FileType
;

@@footer