WITH flashback_database_log AS (SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes,         retention_target retention_target_minutes,         flashback_size / 1048576 flashback_size_mb,         estimated_flashback_size / 1048576 estimated_flashback_size_mb    FROM v$flashback_database_log),flashback_database_logfile AS (SELECT COUNT(*) logs,         SUM(BYTES / 1048576) size_mb,         MIN(first_time) oldest_log,         MAX(first_time) latest_log    FROM v$flashback_database_logfile),flashback_usage AS (SELECT file_type,         ROUND(mb_used, 2) mb_used,         ROUND(mb_reclaimable, 2) mb_reclaimable,         DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used,         DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable,         number_of_files,         total_mb db_recovery_file_dest_mb,         flashback_retention_target,         oldest_record,         ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec    FROM (SELECT SUM(DECODE(NAME,                            'db_recovery_file_dest_size',                            VALUE / 1048576,                            0)) total_mb,                 SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target            FROM v$parameter           WHERE NAME IN ('db_recovery_file_dest_size',                          'db_flashback_retention_target')),         (SELECT 'FLASHBACKLOG' file_type,                 NVL(SUM(BYTES) / 1048576, 0) mb_used,                 sum(CASE                       WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN                        bytes / 1048576                       ELSE                        0                     END) mb_reclaimable,                 COUNT(*) number_of_files,                 MIN(first_time) oldest_record            FROM (select bytes,                         lead(first_time) over(order by first_time asc) last_time,                         first_time                    from v$flashback_database_logfile) fla_log,                 (SELECT value value                    FROM v$parameter                   WHERE name = 'db_flashback_retention_target') tgt          UNION          SELECT 'BACKUPPIECE' file_type,                 NVL(SUM(BYTES / 1048576), 0) mb,                 SUM(CASE                       WHEN dl.rectype = 13 THEN                        (BYTES / 1048576)                       ELSE                        0                     END) reclaimable_mb,                 COUNT(*) no_of_files,                 MIN(start_time) oldest_record            FROM v$backup_piece bp, x$kccagf dl           WHERE is_recovery_dest_file = 'YES'             AND deleted = 'NO'             AND bp.recid = dl.recid(+)             AND dl.rectype(+) = 13          UNION          SELECT 'ARCHIVELOG' file_type,                 NVL(SUM(blocks * block_size) / 1048576, 0) mb,                 SUM(CASE                       WHEN dl.rectype = 11 THEN                        (LOG.blocks * LOG.block_size / 1048576)                       ELSE                        0                     END) reclaimable_mb,                 COUNT(*) no_of_files,                 MIN(first_time) oldest_record            FROM v$archived_log log, x$kccagf dl           WHERE deleted = 'NO'             AND is_recovery_dest_file = 'YES'             AND dl.recid(+) = log.recid             AND dl.rectype(+) = 11          UNION          SELECT 'ONLINELOG' file_type,                 SUM(BYTES / 1048576) mb,                 0 reclaimable,                 COUNT(*) no_of_files,                 MIN(first_time) oldest_record            FROM v$logfile lf,                 (SELECT group#, BYTES, first_time                    FROM v$standby_log                  UNION                  SELECT group#, BYTES, first_time FROM v$log) l           WHERE l.group# = lf.group#             AND lf.is_recovery_dest_file = 'YES'          UNION          SELECT 'IMAGECOPY',                 NVL(SUM(blocks * (block_size / 1048576)), 0) mb,                 0 reclaimable_mb,                 COUNT(*) no_of_files,                 MIN(creation_time) oldest_record            FROM v$datafile_copy           WHERE deleted = 'NO'             AND is_recovery_dest_file = 'YES'          UNION          SELECT 'CONTROLFILE',                 NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb,                 0 reclaimable,                 COUNT(*) no_of_files,                 NULL oldest_record            FROM v$controlfile           WHERE is_recovery_dest_file = 'YES'))SELECT order_, NAME, VALUE  FROM(SELECT 0 order_, NAME, VALUE  FROM v$parameter WHERE NAME LIKE 'db_recovery_file%'UNIONSELECT 3, 'oldest flashback log (minutes)', TO_CHAR(ROUND(oldest_log_minutes, 2))  FROM flashback_database_logUNIONSELECT 1, 'retention target (minutes)', TO_CHAR(ROUND(retention_target_minutes, 2))  FROM flashback_database_logUNIONSELECT 2, 'estimated size for flashback logs (MB)', TO_CHAR(ROUND(estimated_flashback_size_mb, 2))  FROM flashback_database_logUNIONSELECT 2, 'Current flashback log count', TO_CHAR(logs)  FROM flashback_database_logfileUNIONSELECT 3, 'Most recent flashback log (minutes)', TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2))  FROM flashback_database_logfileUNIONSELECT 4, 'Total size of all files in MB', TO_CHAR(ROUND(SUM(mb_used), 2))  FROM flashback_usageUNIONSELECT 5, 'Total size of reclaimable files in MB', TO_CHAR(ROUND(SUM(mb_reclaimable), 2))  FROM flashback_usageUNIONSELECT 6, 'unused space in MB', TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used)))  FROM flashback_usage) ORDER BY order_, NAME/

ORDER_ NAME                                                                             VALUE---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------         0 db_recovery_file_dest                                                            +SYSTEMDG         0 db_recovery_file_dest_size                                                       5218762752         1 retention target (minutes)                                                       1440         2 Current flashback log count                                                      33         2 estimated size for flashback logs (MB)                                           142.15         3 Most recent flashback log (minutes)                                              164.03         3 oldest flashback log (minutes)                                                   5846.35         4 Total size of all files in MB                                                    1963.11         5 Total size of reclaimable files in MB                                            534.47         6 unused space in MB                                                               3014