RMAN

SET lines 200 pages 2000
col STATUS format a20
col dev format a10
col START_TIME format a16
col END_TIME format a16
col hrs format 99.99
col INBYTES format a10
col OUTBYTES format a10
SELECT SESSION_KEY,SESSION_RECID, SESSION_STAMP,INPUT_TYPE, STATUS, output_device_type DEV,
TO_CHAR(START_TIME,'dd/mm/yyyy hh24:mi') start_time,
TO_CHAR(END_TIME,'dd/mm/yyyy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
input_bytes_display inbytes, output_bytes_display outbytes
FROM V$RMAN_BACKUP_JOB_DETAILS
WHERE status='RUNNING'
ORDER BY session_key;

SET lines 220
SET pages 1000
col backup_type FOR a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces FOR 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type FOR a10 TRUNC heading "DEVICE|TYPE"
col compressed FOR a4 heading "ZIP?"
col output_mbytes FOR 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only FOR a4 heading "SCAN|ONLY"
SELECT
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
TO_CHAR(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
TO_CHAR(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
FROM V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s ON s.set_stamp = d.set_stamp AND s.set_count = d.set_count
WHERE session_recid = &SESSION_RECID
AND session_stamp = &SESSION_STAMP
ORDER BY d.start_time;
 
                                                                            ELAPSED DEVICE              OUTPUT SCAN
    BS_KEY TYPE CF?   INCR LVL  PCS START_TIME       COMPLETION_TIME        SECONDS TYPE       ZIP?     MBYTES ONLY
---------- ---- --- ---------- ---- ---------------- ------------------- ---------- ---------- ---- ---------- ----
      3336 L    NO                1 2024-12-02 20:14 2024-12-02 20:15:25         41 SBT_TAPE   YES           1 NO

SET lines 220
SET pages 1000
col cf FOR 9,999
col df FOR 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 FOR 9,999
col i1 FOR 9,999
col l FOR 9,999
col output_mbytes FOR 99,999,999 heading "OUTPUT|MBYTES"
col session_recid FOR 999999 heading "SESSION|RECID"
col session_stamp FOR 99999999999 heading "SESSION|STAMP"
col status FOR a20
col time_taken_display FOR a10 heading "TIME|TAKEN"
col output_instance FOR 9999 heading "OUT|INST"
col DOW format a10
col START_TIME format a20
col END_TIME format a20
SELECT
j.session_recid, j.session_stamp,
TO_CHAR(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
TO_CHAR(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
DECODE(TO_CHAR(j.start_time, 'd'), 1, 'SUNDAY', 2, 'MONDAY',
3, 'TUESDAY', 4, 'WEDNESDAY',
5, 'THURSDAY', 6, 'FRIDAY',
7, 'SATURDAY') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
FROM V$RMAN_BACKUP_JOB_DETAILS j
left outer join (SELECT
d.session_recid, d.session_stamp,
SUM(CASE WHEN d.controlfile_included = 'YES' THEN d.pieces ELSE 0 END) CF,
SUM(CASE WHEN d.controlfile_included = 'NO'
AND d.backup_type||d.incremental_level = 'D' THEN d.pieces ELSE 0 END) DF,
SUM(CASE WHEN d.backup_type||d.incremental_level = 'D0' THEN d.pieces ELSE 0 END) I0,
SUM(CASE WHEN d.backup_type||d.incremental_level = 'I1' THEN d.pieces ELSE 0 END) I1,
SUM(CASE WHEN d.backup_type = 'L' THEN d.pieces ELSE 0 END) L
FROM
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s ON s.set_stamp = d.set_stamp AND s.set_count = d.set_count
WHERE s.input_file_scan_only = 'NO'
GROUP BY d.session_recid, d.session_stamp) x
ON x.session_recid = j.session_recid AND x.session_stamp = j.session_stamp
left outer join (SELECT o.session_recid, o.session_stamp, MIN(inst_id) inst_id
FROM GV$RMAN_OUTPUT o
GROUP BY o.session_recid, o.session_stamp)
ro ON ro.session_recid = j.session_recid AND ro.session_stamp = j.session_stamp
WHERE j.start_time > TRUNC(SYSDATE)-1
ORDER BY j.start_time;