59 lines
2.0 KiB
SQL
59 lines
2.0 KiB
SQL
@@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 |