V$PROXY_ARCHIVELOG_SUMMARY

select
nvl                      -- NUM_FILES_BACKED NUMBER
  (num_files_backed, 0),
distinct_files_backed,   -- NUM_DISTINCT_FILES_BACKED NUMBER
min_first_change#,       -- MIN_FIRST_CHANGE# NUMBER
max_next_change#,        -- MAX_NEXT_CHANGE# NUMBER
min_first_time,          -- MIN_FIRST_TIME DATE
max_next_time,           -- MAX_NEXT_TIME DATE
output_bytes,            -- OUTPUT_BYTES NUMBER
sys.dbms_rcvman.num2displaysize -- OUTPUT_BYTES_DISPLAY VARCHAR2(4000)
  (output_bytes) output_bytes_display 
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,
sum ((blocks+1) *block_size) 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)))
home |  up  |  mailbox