V$BACKUP_ARCHIVELOG_DETAILS

select
a.*,                     -- BTYPE CHAR(9)
sys.dbms_rcvman.num2displaysize -- BTYPE_KEY NUMBER
  (filesize) filesize_display 
from
(
select
unique 'BACKUPSET' btype,
b.recid btype_key,
b.session_recid session_key,
b.session_recid,
b.session_stamp,
a.set_stamp id1,
b.set_count id2,
thread#,
sequence#,
resetlogs_change#,
resetlogs_time,
first_change#,
first_time,
next_change#,
next_time,
(blocks+1) *a.block_size filesize,
case when b.compression_ratio>1 then b.compression_ratio else 1 end compression_ratio 
from
v$backup_redolog a,
v$backup_set_details b 
where
a.set_stamp = b.set_stamp and a.set_count = b.set_count 
union select
unique 'PROXYCOPY',
a.recid btype_key,
session_recid session_key,
session_recid,
session_stamp,
a.recid,
a.stamp,
thread#,
sequence#,
resetlogs_change#,
resetlogs_time,
first_change#,
first_time,
next_change#,
next_time,
(blocks+1) *block_size filesize,
1 
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