V$BACKUP_COPY_SUMMARY

select
a.*,                     -- NUM_COPIES NUMBER
sys.dbms_rcvman.num2displaysize -- NUM_DISTINCT_COPIES NUMBER
  (output_bytes) output_bytes_display 
from
(
select
nvl (sum (num_times_backed), 0) num_copies,
sum (distinct_copies) distinct_copies,
min (min_checkpoint_change#) min_checkpoint_change#,
max (max_checkpoint_change#) max_checkpoint_change#,
min (min_checkpoint_time) min_checkpoint_time,
max (max_checkpoint_time) max_checkpoint_time,
sum (output_bytes) output_bytes 
from
(
select
unique file#,
count (*) over (partition by file#,
creation_change#) num_times_backed,
count (distinct file#) over (partition by file#,
creation_change#,
checkpoint_change#) distinct_copies,
min (checkpoint_change#) over (partition by file#,
creation_change#) min_checkpoint_change#,
max (checkpoint_change#) over (partition by file#,
creation_change#) max_checkpoint_change#,
min (checkpoint_time) over (partition by file#,
creation_change#) min_checkpoint_time,
max (checkpoint_time) over (partition by file#,
creation_change#) max_checkpoint_time,
sum ((blocks+1) *block_size) over (partition by file#,
creation_change#) output_bytes 
from
v$datafile_copy a,
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.status = 'A' and 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))) a
home |  up  |  mailbox