V$BACKUP_SET_DETAILS

select
unique                   -- SESSION_KEY NUMBER
  b.session_recid session_key,
b.session_recid,         -- SESSION_RECID NUMBER
b.session_stamp,         -- SESSION_STAMP NUMBER
a.recid bs_key,          -- BS_KEY NUMBER
a.RECID,                 -- RECID NUMBER
a.stamp,                 -- STAMP NUMBER
a.set_stamp,             -- SET_STAMP NUMBER
a.set_count,             -- SET_COUNT NUMBER
a.backup_type,           -- BACKUP_TYPE VARCHAR2(1)
a.controlfile_included,  -- CONTROLFILE_INCLUDED VARCHAR2(3)
a.incremental_level,     -- INCREMENTAL_LEVEL NUMBER
a.pieces,                -- PIECES NUMBER
a.start_time,            -- START_TIME DATE
a.completion_time,       -- COMPLETION_TIME DATE
a.elapsed_seconds,       -- ELAPSED_SECONDS NUMBER
a.block_size,            -- BLOCK_SIZE NUMBER
a.keep,                  -- KEEP VARCHAR2(3)
a.keep_until,            -- KEEP_UNTIL DATE
a.keep_options,          -- KEEP_OPTIONS VARCHAR2(10)
a.device_type,           -- DEVICE_TYPE VARCHAR2(17)
a.compressed,            -- COMPRESSED VARCHAR2(3)
a.num_copies,            -- NUM_COPIES NUMBER
a.output_bytes,          -- OUTPUT_BYTES NUMBER
a.original_input_bytes,  -- ORIGINAL_INPUT_BYTES NUMBER
case                     -- COMPRESSION_RATIO NUMBER
  when a.compression_ratio > 1 then a.compression_ratio else 1 end,
'A' status,              -- STATUS CHAR(1)
a.original_inprate_bytes, -- ORIGINAL_INPRATE_BYTES NUMBER
a.output_rate_bytes,     -- OUTPUT_RATE_BYTES NUMBER
sys.dbms_rcvman.num2displaysize -- ORIGINAL_INPUT_BYTES_DISPLAY VARCHAR2(4000)
  (original_input_bytes) original_input_bytes_display,
sys.dbms_rcvman.num2displaysize -- OUTPUT_BYTES_DISPLAY VARCHAR2(4000)
  (output_bytes) output_bytes_display,
sys.dbms_rcvman.num2displaysize -- ORIGINAL_INPRATE_BYTES_DISPLAY VARCHAR2(4000)
  (original_inprate_bytes) original_inprate_bytes_display,
sys.dbms_rcvman.num2displaysize -- OUTPUT_RATE_BYTES_DISPLAY VARCHAR2(4000)
  (output_rate_bytes) output_rate_bytes_display,
sys.dbms_rcvman.sec2displaytime -- TIME_TAKEN_DISPLAY VARCHAR2(4000)
  (elapsed_seconds) time_taken_display 
from
(
select
unique a.*,
b.rman_status_recid,
b.rman_status_stamp,
decode (b.devcnt, 1, first_value (b.device_type) over (partition by b.set_stamp, b.set_count), '*') device_type,
b.compressed,
count (distinct copy#) over (partition by b.set_stamp,
b.set_count) num_copies,
b.output_bytes output_bytes,
c.original_input_bytes,
c.original_input_bytes / (decode (b.output_bytes, 0, c.original_input_bytes, b.output_bytes)) compression_ratio,
c.original_input_bytes/ (decode (a.elapsed_seconds, 0, 1, a.elapsed_seconds)) original_inprate_bytes,
b.output_bytes/ (decode (a.elapsed_seconds, 0, 1, a.elapsed_seconds)) output_rate_bytes 
from
v$backup_set a,
(
select
set_stamp,
set_count,
device_type,
status,
count (distinct device_type) over (partition by set_count,
set_stamp) devcnt,
compressed,
sum (bytes) over (partition by set_count,
set_stamp,
copy#) output_bytes,
copy#,
RMAN_STATUS_RECID,
RMAN_STATUS_STAMP,
count (piece#) over (partition by set_count,
set_stamp,
copy#) npieces 
from
v$backup_piece 
where
status = 'A') b,
(
select
set_stamp,
set_count,
sum (original_input_bytes) original_input_bytes 
from
(
select
set_stamp,
set_count,
sum ((datafile_blocks+1) *block_size) over (partition by set_count,
set_stamp) original_input_bytes 
from
v$backup_datafile 
union select
set_stamp,
set_count,
sum (bytes) over (partition by set_count,
set_stamp) original_input_bytes 
from
v$backup_spfile) 
group by set_stamp,
set_count 
union select
set_stamp,
set_count,
sum ((blocks+1) *block_size) over (partition by set_count,
set_stamp) original_input_bytes 
from
v$backup_redolog) c 
where
a.set_stamp=b.set_stamp and a.set_stamp=b.set_stamp and a.set_stamp=c.set_stamp and a.set_stamp=c.set_stamp and a.pieces=b.npieces) a,
(
select
session_recid,
session_stamp,
recid,
stamp,
start_time,
end_time 
from
v$rman_status) b,
(
select
/*+ no_merge */ 
sys.dbms_rcvman.sv_getsessionkey skey 
from
dual) c,
(
select
/*+ no_merge */ 
sys.dbms_rcvman.sv_getsessionfromTimeRange fTime 
from
dual) d,
(
select
/*+ no_merge */ 
sys.dbms_rcvman.sv_getsessionuntilTimeRange uTime 
from
dual) e 
where
a.rman_status_recid = b.recid (+) and a.rman_status_stamp = b.stamp (+) and (c.skey is null or c.skey = b.session_recid) and (d.fTime is null or d.fTime <= b.start_time) and (e.uTime is null or e.uTime >= b.end_time)
home |  up  |  mailbox