V$RMAN_BACKUP_SUBJOB_DETAILS

select
a.session_recid          -- SESSION_KEY NUMBER
  session_key,
a.*,                     -- SESSION_RECID NUMBER
decode                   -- SESSION_STAMP NUMBER
  (nvl (b.autocnt, 0), 0, 'NO', 'YES') autobackup_done,
decode                   -- OPERATION VARCHAR2(33)
  (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                   -- COMMAND_ID VARCHAR2(33)
  (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) object_type,
decode                   -- START_TIME DATE
  (optimized_weight, 1, 'YES', 'NO') optimized,
nvl                      -- END_TIME DATE
  (b.autocnt, 0) autobackup_count,
case                     -- INPUT_BYTES NUMBER
  when input_bytes/decode (output_bytes, 0, null, output_bytes) > 1 then input_bytes/decode (output_bytes, 0, null, output_bytes) else 1 end compression_ratio,
sys.dbms_rcvman.num2displaysize -- OUTPUT_BYTES NUMBER
  (input_bytes) input_bytes_display,
sys.dbms_rcvman.num2displaysize -- STATUS_WEIGHT NUMBER
  (output_bytes) output_bytes_display 
from
(
select
unique session_recid,
session_stamp,
operation,
command_id,
min (start_time) over (partition by session_recid,
session_stamp,
operation) start_time,
max (end_time) over (partition by session_recid,
session_stamp,
operation) end_time,
sum (input_bytes) over (partition by session_recid,
session_stamp,
operation) input_bytes,
sum (output_bytes) over (partition by session_recid,
session_stamp,
operation) output_bytes,
max (status_weight) over (partition by session_recid,
session_stamp,
operation) status_weight,
max (object_type_weight) over (partition by session_recid,
session_stamp,
operation) object_type_weight,
max (optimized_weight) over (partition by session_recid,
session_stamp,
operation) optimized_weight,
decode (count (distinct output_device_type) over (partition by session_recid, session_stamp, operation), 1, first_value (output_device_type) over (partition by session_recid, session_stamp, operation), 0, null, '*') output_device_type 
from
(
select
d.*,
decode (status, 'RUNNING', 1001, 'RUNNING WITH WARNINGS', 1500, 'RUNNING WITH ERRORS', 1900, 'COMPLETED', 0001, 'COMPLETED WITH WARNINGS', 500, 'COMPLETED WITH ERRORS', 900, 'FAILED', 2000, 2000) status_weight,
decode (object_type, 'DB FULL', 9, 'RECVR AREA', 8, 'DB INCR', 7, 'DATAFILE FULL', 6, 'DATAFILE INCR', 5, 'ARCHIVELOG', 4, 'CONTROLFILE', 3, 'SPFILE', 2, 'BACKUPSET', 1, 0) object_type_weight,
decode (optimized, 'YES', 1, 0) optimized_weight 
from
v$rman_status d 
where
operation like 'BACKUP%' and row_level=1)) a,
(
select
session_recid,
session_stamp,
count (*) autocnt 
from
v$rman_status 
where
operation like '%AUTOBACKUP%' and row_level > 1 
group by session_recid,
session_stamp) b 
where
a.session_recid=b.session_recid (+) and a.session_stamp=b.session_stamp (+)
home |  up  |  mailbox