V$BACKUP_DATAFILE_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 -- NUM_DISTINCT_TS_BACKED NUMBER (input_bytes) input_bytes_display, sys.dbms_rcvman.num2displaysize -- MIN_CHECKPOINT_CHANGE# NUMBER (output_bytes) output_bytes_display from ( select sum (num_times_backed) num_files_backed, count (*) num_distinct_files_backed, count (distinct ts#) num_distinct_ts_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 a.*, b.ts# from ( select unique a.file#, sum (a.num_times_backed) num_times_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, creation_change# from (( select unique file#, count (*) over (partition by file#, creation_change#) num_times_backed, 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 ((datafile_blocks+1) *block_size) over (partition by file#, creation_change#) input_bytes, sum ((blocks+1) *block_size) over (partition by file#, creation_change#) output_bytes, creation_change# fromv$backup_datafile where file# <> 0 and (set_stamp, set_count) in ( select set_stamp, set_count from v$backup_set_details)) union ( select unique file#, count (*) over (partition by file#, creation_change#) num_times_backed, 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#) input_bytes, sum ((blocks+1) *block_size) over (partition by file#, creation_change#) output_bytes, creation_change# 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 file#, count (*) over (partition by file#, creation_change#) num_times_backed, 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 (chec

home |  up  |  mailbox