V$BACKUP_CONTROLFILE_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,
creation_time,
resetlogs_change#,
resetlogs_time,
checkpoint_change#,
checkpoint_time,
(datafile_blocks+1) *a.block_size filesize,
1 compression_ratio 
from
v$backup_datafile a,
v$backup_set_details b 
where
a.set_stamp = b.set_stamp and a.set_count = b.set_count and file# = 0 
union select
unique 'IMAGECOPY' btype,
a.recid btype_key,
b.session_recid session_key,
b.session_recid,
b.session_stamp,
a.recid,
a.stamp,
creation_time,
resetlogs_change#,
resetlogs_time,
checkpoint_change#,
checkpoint_time,
(blocks+1) *block_size filesize,
1 compression_ratio 
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 'PROXYCOPY' btype,
a.recid btype_key,
b.session_recid session_key,
b.session_recid,
b.session_stamp,
a.recid,
a.stamp,
creation_time,
resetlogs_change#,
resetlogs_time,
checkpoint_change#,
checkpoint_time,
(blocks+1) *block_size filesize,
1 compression_ratio 
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
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)) a
home |  up  |  mailbox