V$BACKUP_DATAFILE_DETAILS

select
a.*,                     -- BTYPE CHAR(9)
b.ts#,                   -- BTYPE_KEY NUMBER
b.name tsname,           -- SESSION_KEY NUMBER
sys.dbms_rcvman.num2displaysize -- SESSION_RECID 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,
file#,
creation_change#,
creation_time,
resetlogs_change#,
resetlogs_time,
a.incremental_level,
incremental_change#,
checkpoint_change#,
checkpoint_time,
marked_corrupt,
(datafile_blocks+1) *a.block_size filesize,
(datafile_blocks+1) / (blocks+1) 
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,
file#,
creation_change#,
creation_time,
resetlogs_change#,
resetlogs_time,
incremental_level,
0 incremental_change#,
checkpoint_change#,
checkpoint_time,
marked_corrupt,
(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,
file#,
creation_change#,
creation_time,
resetlogs_change#,
resetlogs_time,
incremental_level,
0 incremental_change#,
checkpoint_change#,
checkpoint_time,
null marked_corrupt,
(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,
(
select
df.file#,
df.ts#,
ts.name 
from
v$datafile df,
v$tablespace ts 
where
ts.ts# = df.ts#) b 
where
a.file# = b.file# (+)
home |  up  |  mailbox