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

WITH backup_status AS (

    SELECT

        DECODE(b.incremental_level,

               NULL, 'FULL',

               0, 'LEVEL 0 INCR',

               1, 'LEVEL 1 INCR',

               'OTHER') AS backup_type,

        CASE

            WHEN bs.status = 'A' THEN 1  -- Available (Success)

            ELSE 0

        END AS is_success,

        CASE

            WHEN bs.status <> 'A' THEN 1

            ELSE 0

        END AS is_failed

    FROM 

        v$backup_set b

        JOIN v$backup_set_details bs

          ON b.set_stamp = bs.set_stamp AND b.set_count = bs.set_count

    WHERE 

        b.start_time >= SYSDATE - 7  -- adjust as needed

)

SELECT

    backup_type,

    SUM(is_success) AS success_count,

    SUM(is_failed) AS failed_count,

    COUNT(*) AS total,

    ROUND((SUM(is_success) * 100.0) / COUNT(*), 2) AS success_rate_pct

FROM

    backup_status

GROUP BY

    backup_type

ORDER BY

    backup_type;