V$BACKUP_CONTROLFILE_SUMMARY

select
a.*,                     -- NUM_FILES_BACKED NUMBER
case                     -- NUM_DISTINCT_FILES_BACKED 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 -- MIN_CHECKPOINT_CHANGE# NUMBER
  (input_bytes) input_bytes_display,
sys.dbms_rcvman.num2displaysize -- MAX_CHECKPOINT_CHANGE# NUMBER
  (output_bytes) output_bytes_display 
from
(
select
sum (num_times_backed) num_files_backed,
1 num_distinct_files_backed,
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 (input_bytes) input_bytes,
sum (output_bytes) output_bytes 
from
((
select
unique count (*) over (partition by creation_time) num_times_backed,
min (checkpoint_change#) over (partition by creation_time) min_checkpoint_change#,
max (checkpoint_change#) over (partition by creation_time) max_checkpoint_change#,
min (checkpoint_time) over (partition by creation_time) min_checkpoint_time,
max (checkpoint_time) over (partition by creation_time) max_checkpoint_time,
sum ((datafile_blocks+1) *block_size) over (partition by creation_time) input_bytes,
sum ((blocks+1) *block_size) over (partition by creation_time) output_bytes,
creation_time 
from
v$backup_datafile 
where
file# = 0 and (set_stamp,
set_count) in (
select
set_stamp,
set_count 
from
v$backup_set_details)) 
union (
select
unique count (*) over (partition by creation_time) num_times_backed,
min (checkpoint_change#) over (partition by creation_time) min_checkpoint_change#,
max (checkpoint_change#) over (partition by creation_time) max_checkpoint_change#,
min (checkpoint_time) over (partition by creation_time) min_checkpoint_time,
max (checkpoint_time) over (partition by creation_time) max_checkpoint_time,
sum ((blocks+1) *block_size) over (partition by creation_time) input_bytes,
sum ((blocks+1) *block_size) over (partition by creation_time) output_bytes,
creation_time 
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.file# = 0 and 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)) 
union (
select
unique count (*) over (partition by creation_time) num_times_backed,
min (checkpoint_change#) over (partition by creation_time) min_checkpoint_change#,
max (checkpoint_change#) over (partition by creation_time) max_checkpoint_change#,
min (checkpoint_time) over (partition by creation_time) min_checkpoint_time,
max (checkpoint_time) over (partition by creation_time) max_checkpoint_time,
sum ((blocks+1) *block_size) over (partition by creation_time) input_bytes,
sum ((blocks+1) *block_size) over (partition by creation_time) output_bytes,
creation_time 
from
v$proxy_datafile 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
dua
home |  up  |  mailbox