V$BACKUP_ARCHIVELOG_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_FIRST_CHANGE# NUMBER
  (input_bytes) input_bytes_display,
sys.dbms_rcvman.num2displaysize -- MAX_NEXT_CHANGE# NUMBER
  (output_bytes) output_bytes_display 
from
(
select
sum (num_files_backed) num_files_backed,
sum (distinct_files_backed) distinct_files_backed,
min (min_first_change#) min_first_change#,
max (max_next_change#) max_next_change#,
min (min_first_time) min_first_time,
max (max_next_time) max_next_time,
sum (original_input_bytes) input_bytes,
sum (output_bytes) output_bytes 
from
((
select
num_files_backed,
distinct_files_backed,
min_first_change#,
max_next_change#,
min_first_time,
max_next_time,
original_input_bytes,
output_bytes 
from
(
select
count (*) num_files_backed,
min (first_change#) min_first_change#,
max (next_change#) max_next_change#,
min (first_time) min_first_time,
max (next_time) max_next_time 
from
v$backup_redolog 
where
(set_stamp,
set_count) in (
select
set_stamp,
set_count 
from
v$backup_set_details)),
(
select
count (*) distinct_files_backed 
from
(
select
unique thread#,
sequence#,
resetlogs_change#,
resetlogs_time 
from
v$backup_redolog 
where
(set_stamp,
set_count) in (
select
set_stamp,
set_count 
from
v$backup_set_details))),
(
select
nvl (sum (original_input_bytes), 0) original_input_bytes,
nvl (sum (output_bytes), 0) output_bytes 
from
(
select
unique set_count,
set_stamp,
original_input_bytes,
output_bytes 
from
v$backup_set_details 
where
backup_type='L'))) 
union (
select
num_files_backed,
distinct_files_backed,
min_first_change#,
max_next_change#,
min_first_time,
max_next_time,
original_input_bytes,
output_bytes 
from
(
select
count (*) num_files_backed,
min (first_change#) min_first_change#,
max (next_change#) max_next_change#,
min (first_time) min_first_time,
max (next_time) max_next_time,
nvl (sum ((blocks+1) *block_size), 0) original_input_bytes,
nvl (sum ((blocks+1) *block_size), 0) output_bytes 
from
v$proxy_archivedlog 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)),
(
select
count (*) distinct_files_backed 
from
(
select
unique thread#,
sequence#,
resetlogs_change#,
resetlogs_time 
from
v$proxy_archivedlog 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