V$RMAN_BACKUP_JOB_DETAILS

select
a.*,                     -- SESSION_KEY NUMBER
sys.dbms_rcvman.num2displaysize -- SESSION_RECID NUMBER
  (input_bytes) input_bytes_display,
sys.dbms_rcvman.num2displaysize -- SESSION_STAMP NUMBER
  (output_bytes) output_bytes_display,
sys.dbms_rcvman.num2displaysize -- COMMAND_ID VARCHAR2(33)
  (input_bytes_per_sec) input_bytes_per_sec_display,
sys.dbms_rcvman.num2displaysize -- START_TIME DATE
  (output_bytes_per_sec) output_bytes_per_sec_display,
sys.dbms_rcvman.sec2displaytime -- END_TIME DATE
  (elapsed_seconds) time_taken_display 
from
(
select
unique a.session_recid session_key,
a.*,
decode (autobackup_count, 0, 'NO', 'YES') autobackup_done,
decode (status_weight, 2000, 'FAILED', 1900, 'RUNNING WITH ERRORS', 1500, 'RUNNING WITH WARNINGS', 1001, 'RUNNING', 900, 'COMPLETED WITH ERRORS', 500, 'COMPLETED WITH WARNINGS', 001, 'COMPLETED', 'FAILED') status,
decode (object_type_weight, 9, 'DB FULL', 8, 'RECVR AREA', 7, 'DB INCR', 6, 'DATAFILE FULL', 5, 'DATAFILE INCR', 4, 'ARCHIVELOG', 3, 'CONTROLFILE', 2, 'SPFILE', 1, 'BACKUPSET', null) input_type,
decode (optimized_weight, 1, 'YES', 'NO') optimized,
abs (a.end_time-a.start_time) *86400 elapsed_seconds,
case when a.input_bytes/decode (a.output_bytes, 0, null, a.output_bytes) > 1 then a.input_bytes/decode (a.output_bytes, 0, null, a.output_bytes) else 1 end compression_ratio,
a.input_bytes/ (decode (a.end_time-a.start_time, 0, 1, abs (a.end_time-a.start_time) *86400)) input_bytes_per_sec,
a.output_bytes/ (decode (a.end_time-a.start_time, 0, 1, abs (a.end_time-a.start_time) *86400)) output_bytes_per_sec 
from
(
select
session_recid,
session_stamp,
command_id,
min (start_time) over (partition by session_recid,
session_stamp) start_time,
max (end_time) over (partition by session_recid,
session_stamp) end_time,
sum (input_bytes) over (partition by session_recid,
session_stamp) input_bytes,
sum (output_bytes) over (partition by session_recid,
session_stamp) output_bytes,
max (status_weight) over (partition by session_recid,
session_stamp) status_weight,
max (optimized_weight) over (partition by session_recid,
session_stamp) optimized_weight,
max (object_type_weight) over (partition by session_recid,
session_stamp) object_type_weight,
decode (count (distinct output_device_type) over (partition by session_recid, session_stamp), 1, first_value (output_device_type) over (partition by session_recid, session_stamp), 0, null, '*') output_device_type,
sum (autobackup_count) over (partition by session_recid,
session_stamp) autobackup_count 
from
V$RMAN_BACKUP_SUBJOB_DETAILS) a) a
home |  up  |  mailbox