V$UNUSABLE_BACKUPFILE_DETAILSselect b.session_recid -- SESSION_KEY NUMBER session_key, b.session_recid, -- SESSION_RECID NUMBER b.session_stamp, -- SESSION_STAMP NUMBER a.* -- RMAN_STATUS_RECID NUMBER from ( select a.rman_status_recid, a.rman_status_stamp, 'BACKUPSET' btype, b.recid btype_key, a.set_stamp id1, a.set_count id2, 'BACKUPPIECE' filetype, b.recid filetype_key, a.status, a.bytes filesize, a.device_type, a.handle filename, a.media, a.media_pool from v$backup_piece a, v$backup_set b where a.set_stamp = b.set_stamp and a.set_count = b.set_count and status <> 'A' union select rman_status_recid, rman_status_stamp, 'IMAGECOPY', recid, recid, stamp, 'DATAFILECOPY', recid, status, (blocks+1) *block_size, 'DISK', name, null, null from v$datafile_copy where status <> 'A' and file#<>0 union select rman_status_recid, rman_status_stamp, 'IMAGECOPY', recid, recid, stamp, 'CONTROLFILECOPY', recid, status, (blocks+1) *block_size, 'DISK', name, null, null from v$datafile_copy where status <> 'A' and file#=0 union select rman_status_recid, rman_status_stamp, 'PROXYCOPY', recid, recid, stamp, 'DATAFILECOPY', recid, status, (blocks+1) *block_size, device_type, handle, media, media_pool from v$proxy_datafile where status <> 'A' and file#<>0 union select rman_status_recid, rman_status_stamp, 'PROXYCOPY', recid, recid, stamp, 'CONTROLFILECOPY', recid, status, (blocks+1) *block_size, device_type, handle, media, media_pool from v$proxy_datafile where status <> 'A' and file#=0 union select rman_status_recid, rman_status_stamp, 'PROXYCOPY', recid, recid, stamp, 'ARCHIVELOGCOPY', recid, status, (blocks+1) *block_size, device_type, handle, media, media_pool from v$proxy_archivedlog where status <> 'A') 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.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)