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