JAVA J2EE 4 U

SET LINESIZE 200

COL backup_type FOR A15

COL success_count FOR 15

COL failed_count FOR 15

COL total FOR 10

COL success_rate_pct FOR 20

SELECT

    DECODE(backup_type,

           'DB FULL', 'FULL',

           'DB INCR', 'INCREMENTAL',

           'OTHER') AS backup_type,

    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS success_count,

    COUNT(CASE WHEN status <> 'COMPLETED' THEN 1 END) AS failed_count,

    COUNT(*) AS total,

    ROUND(

        (COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) * 100.0) / 

         COUNT(*), 2) AS success_rate_pct

FROM 

    v$rman_backup_job_details

WHERE 

    start_time >= SYSDATE - 7

    AND backup_type IN ('DB FULL', 'DB INCR')

GROUP BY

    DECODE(backup_type,

           'DB FULL', 'FULL',

           'DB INCR', 'INCREMENTAL',

           'OTHER')

ORDER BY

    backup_type;