V$FLASH_RECOVERY_AREA_USAGE

select
fusg.file_type,          -- FILE_TYPE VARCHAR2(12)
decode                   -- PERCENT_SPACE_USED NUMBER
  (nvl2 (ra.name, ra.space_limit, 0), 0, 0, round (nvl (fusg.space_used, 0) /ra.space_limit, 4) * 100),
decode                   -- PERCENT_SPACE_RECLAIMABLE NUMBER
  (nvl2 (ra.name, ra.space_limit, 0), 0, 0, round (decode (fusg.file_type, 'FLASHBACKLOG', flb.flbrecl, nvl (fusg.space_reclaimable, 0)) / ra.space_limit, 4) * 100),
nvl2 (ra.name,
fusg.number_of_files,
0)                       -- NUMBER_OF_FILES NUMBER
from
v$recovery_file_dest ra,
(
select
nvl ((
select
fblogreclsiz from x$krfblog), 0) flbrecl 
from
x$dual) flb,
(
select
'CONTROLFILE' file_type,
sum (cfbsz * cffsz) space_used,
0 space_reclaimable,
count (*) number_of_files 
from
x$kcccf 
where
bitand (cffl2, 1) != 0 
union all 
select
'ONLINELOG' file_type,
sum (l.bytes) space_used,
0 space_reclaimable,
count (*) number_of_files 
from
v$log l,
v$logfile lf 
where
l.group# = lf.group# and lf.is_recovery_dest_file = 'YES' 
union all 
select
'ARCHIVELOG' file_type,
sum (al.blocks * al.block_size) space_used,
sum (case when dl.rectype = 11 then (al.blocks * al.block_size) else 0 end) space_reclaimable,
count (*) number_of_files 
from
v$archived_log al,
x$kccagf dl 
where
al.is_recovery_dest_file = 'YES' and al.recid = dl.recid (+) and dl.rectype (+) = 11 and al.name is not null 
union all 
select
'BACKUPPIECE' file_type,
sum (bp.bytes) space_used,
sum (case when dl.rectype = 13 then bp.bytes else 0 end) space_reclaimable,
count (*) number_of_files 
from
v$backup_piece bp,
x$kccagf dl 
where
bp.is_recovery_dest_file = 'YES' and bp.recid = dl.recid (+) and dl.rectype (+) = 13 and bp.handle is not null 
union all 
select
'IMAGECOPY' file_type,
sum (dc.blocks * dc.block_size) space_used,
home |  up  |  mailbox