V$BACKUP_PIECE_DETAILS

select
unique                   -- SESSION_KEY NUMBER
  b.session_recid session_key,
b.session_recid,         -- SESSION_RECID NUMBER
b.session_stamp,         -- SESSION_STAMP NUMBER
a.*,                     -- BS_KEY NUMBER
sys.dbms_rcvman.num2displaysize -- BP_KEY NUMBER
  (bytes) size_bytes_display 
from
(
select
a.recid bs_key,
c.recid bp_key,
c.* 
from
v$backup_set a,
(
select
v$backup_piece.*,
count (piece#) over (partition by set_count,
set_stamp,
copy#) pieces_per_set 
from
v$backup_piece 
where
status = 'A') c 
where
a.set_stamp = c.set_stamp and a.set_count = c.set_count and a.pieces = c.pieces_per_set) a,
(
select
session_recid,
session_stamp,
recid,
stamp,
start_time,
end_time 
from
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.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