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 /