set pages 1000 lines 132
col RECID			format 99999	heading "RecID"
col SET_STAMP			format 9999999999 heading "Set Stamp"
col SET_COUNT			format 9999	heading "Count"
col BACKUP_TYPE			format a9	heading "Backup|Type"
col CONTROLFILE_INCLUDED	format a4	heading "Incl|Ctrl"
col INCREMENTAL_LEVEL		format 9999	heading "Levl"
col PIECES			format 99999	heading "Num|Piece"
col START_TIME			format a15	heading "Started at"
col COMPLETION_TIME		format a15	heading "Finished at"
col ELAPSED_MINS		format 9999.9	heading "Elapsed|Mins"
col BLOCK_SIZE			format 99999	heading "Block|Size"
col INPUT_FILE_SCAN_ONLY	format a5	heading "File|Scan|Only"
col NUM_FILES			format 999	heading "Num|Fil"
col BACKUP_MB			format 999,999	heading "Backed|Up Mb"
col MB_SEC			format 9999.9	heading "Mb/Min"
col MEDIA			format a9	heading "Media"		trunc
col IS_CORRUPT			format a7	heading "Contains|Corrupt|Blocks"

comp avg label 'Average:' of ELAPSED_MINS on report
comp avg sum  label 'Total:' of BACKUP_MB on report
comp avg label 'Average:' of MB_SEC on report

-- break on SET_STAMP skip on BACKUP_TYPE skip on START_TIME skip on report skip

select
--	 bs.RECID	-- }
--	,bs.STAMP	-- } PK for BACKUP_SET
	 bs.SET_STAMP	-- )
	,bs.SET_COUNT	-- ) FK to other backup tables
	,decode( bs.BACKUP_TYPE
		,'L',	'Arch Redo'
		,'I',	'Data Incr'
		,'D',	decode( bs.INCREMENTAL_LEVEL
				,0,	'DF Full'
				,null,	'Data Full',
				'DF Incr ' || bs.INCREMENTAL_LEVEL )
		) as BACKUP_TYPE 
	,bs.CONTROLFILE_INCLUDED
	,bs.INCREMENTAL_LEVEL as INCREMENTAL_LEVEL
	,bs.PIECES
	,to_char( bs.START_TIME,'dd/MM/YY HH24:MI' ) as START_TIME
	,to_char( bs.COMPLETION_TIME,'dd/MM/YY HH24:MI' ) as COMPLETION_TIME
	,bs.ELAPSED_SECONDS/60 as ELAPSED_MINS
	,bs.BLOCK_SIZE
	,af.NUM_FILES
	,af.BACKUP_BLOCKS as BACKUP_MB
	,decode (bs.ELAPSED_SECONDS,0,af.BACKUP_BLOCKS,round( af.BACKUP_BLOCKS/(bs.ELAPSED_SECONDS/60), 1)) as MB_SEC
	,nvl(af.IS_CORRUPT,'n/a') as IS_CORRUPT
	,bp.MEDIA
from	 V$BACKUP_SET	bs
	,V$BACKUP_PIECE	bp
	,( select	 SET_STAMP
			,SET_COUNT
			,sum( BLOCKS/BLOCK_SIZE ) as BACKUP_BLOCKS
			,count(*) as NUM_FILES
			,min(first_change#) as MIN_SCN
			,max(next_change#)-1 as MAX_SCN
			,'' as IS_CORRUPT
		from	 V$BACKUP_REDOLOG
		group by SET_STAMP
			,SET_COUNT
	   union all
	   select	 SET_STAMP
	   		,SET_COUNT
	   		,sum( ( DATAFILE_BLOCKS*BLOCK_SIZE )/1024/1024 ) as BACKUP_BLOCKS
	   		,count(*) as NUM_FILES
	   		,min( INCREMENTAL_CHANGE# ) as MIN_SCN
	   		,max( CHECKPOINT_CHANGE# ) as MAX_SCN
	   		,decode( sum( MARKED_CORRUPT )
	   			,0, 'No'
	   			,'=>Yes<=' ) as IS_CORRUPT
	   	from	V$BACKUP_DATAFILE
	   	group by SET_STAMP
	   		,SET_COUNT ) af
where	(	trunc( bs.START_TIME ) >= to_date(nvl('&STRTDATE',TO_CHAR(SYSDATE-3,'DD-Mon-YYYY')),'DD-Mon-YYYY')
	  and	trunc( bs.COMPLETION_TIME ) <= to_date(nvl('&COMPDATE',TO_CHAR(SYSDATE+1,'DD-Mon-YYYY')),'DD-Mon-YYYY') )
and	af.SET_STAMP = bs.SET_STAMP
and	af.SET_COUNT = bs.SET_COUNT
and	bp.SET_STAMP = bs.SET_STAMP
and	bp.SET_COUNT = bs.SET_COUNT
order by bs.SET_STAMP
	,bs.SET_COUNT
/