@@header
set term off
/*
*
*  Author  : Vishal Gupta
*  Purpose : Display RMAN backup with errors
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  23-Jun-08  Vishal Gupta  First Draft
*/
set term on

COLUMN start_Time FORMAT a18
COLUMN status FORMAT a25
COLUMN object_type FORMAT a15
COLUMN row_type FORMAT a20
COLUMN output_device_Type FORMAT a8 HEADING "Media"
COLUMN input_mb  HEADING "Input|(MB)" FORMAT 999,990.99
COLUMN output_mb HEADING "Output|(MB)" FORMAT 999,990.99
COLUMN duration  HEADING "Dur|(Min)" FORMAT 990.99
SELECT to_char(s.end_Time,'dd-MON-YY hh24:mi:SS') start_time
      ,s.db_name
      ,s.status
   --  ,s.row_type
      ,s.operation
      ,s.object_type
      ,s.output_device_Type
      ,ROUND(s.input_bytes/1024/1024,2) input_mb
      ,ROUND(s.output_bytes/1024/1024,2) output_mb
      ,ROUND( (s.end_time - s.start_time) * 24 * 60,2) duration
FROM rc_rman_status s
WHERE s.end_time > sysdate - 1 + (1/24)
  AND s.status NOT IN  ('COMPLETED'
                       ,'RUNNING'
                       ,'COMPLETED WITH WARNINGS'
                       )
ORDER BY start_time asc;

@@footer