Log switch size



Log switch daily (size)


SET LINESIZE 250
SET PAGESIZE 9999
SET TRIMSPOOL ON
SET VERIFY   off
COLUMN H00   FORMAT 99,999     HEADING '00'
COLUMN H01   FORMAT 99,999     HEADING '01'
COLUMN H02   FORMAT 99,999     HEADING '02'
COLUMN H03   FORMAT 99,999     HEADING '03'
COLUMN H04   FORMAT 99,999     HEADING '04'
COLUMN H05   FORMAT 99,999     HEADING '05'
COLUMN H06   FORMAT 99,999     HEADING '06'
COLUMN H07   FORMAT 99,999     HEADING '07'
COLUMN H08   FORMAT 99,999     HEADING '08'
COLUMN H09   FORMAT 99,999     HEADING '09'
COLUMN H10   FORMAT 99,999     HEADING '10'
COLUMN H11   FORMAT 99,999     HEADING '11'
COLUMN H12   FORMAT 99,999     HEADING '12'
COLUMN H13   FORMAT 99,999     HEADING '13'
COLUMN H14   FORMAT 99,999     HEADING '14'
COLUMN H15   FORMAT 99,999     HEADING '15'
COLUMN H16   FORMAT 99,999     HEADING '16'
COLUMN H17   FORMAT 99,999     HEADING '17'
COLUMN H18   FORMAT 99,999     HEADING '18'
COLUMN H19   FORMAT 99,999     HEADING '19'
COLUMN H20   FORMAT 99,999     HEADING '20'
COLUMN H21   FORMAT 99,999     HEADING '21'
COLUMN H22   FORMAT 99,999     HEADING '22'
COLUMN H23   FORMAT 99,999     HEADING '23'
COLUMN TOTAL FORMAT 999,999    HEADING 'Total'
SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                                                               DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',ROUND(((blocks*block_size)/1024/1024)),0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',ROUND(((blocks*block_size)/1024/1024)),0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',ROUND(((blocks*block_size)/1024/1024)),0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',ROUND(((blocks*block_size)/1024/1024)),0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',ROUND(((blocks*block_size)/1024/1024)),0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',ROUND(((blocks*block_size)/1024/1024)),0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',ROUND(((blocks*block_size)/1024/1024)),0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',ROUND(((blocks*block_size)/1024/1024)),0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',ROUND(((blocks*block_size)/1024/1024)),0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',ROUND(((blocks*block_size)/1024/1024)),0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',ROUND(((blocks*block_size)/1024/1024)),0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',ROUND(((blocks*block_size)/1024/1024)),0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',ROUND(((blocks*block_size)/1024/1024)),0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',ROUND(((blocks*block_size)/1024/1024)),0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',ROUND(((blocks*block_size)/1024/1024)),0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',ROUND(((blocks*block_size)/1024/1024)),0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',ROUND(((blocks*block_size)/1024/1024)),0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',ROUND(((blocks*block_size)/1024/1024)),0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',ROUND(((blocks*block_size)/1024/1024)),0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',ROUND(((blocks*block_size)/1024/1024)),0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',ROUND(((blocks*block_size)/1024/1024)),0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',ROUND(((blocks*block_size)/1024/1024)),0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',ROUND(((blocks*block_size)/1024/1024)),0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',ROUND(((blocks*block_size)/1024/1024)),0)) H23
  , ROUND(SUM((blocks*block_size)/1024/1024))                                                                                                           TOTAL
FROM
  v$archived_log  a
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
/

<br />
SET LINESIZE 250<br />
SET PAGESIZE 9999<br />
SET TRIMSPOOL ON<br />
SET VERIFY   off<br />
COLUMN H00   FORMAT 99,999     HEADING '00'<br />
COLUMN H01   FORMAT 99,999     HEADING '01'<br />
COLUMN H02   FORMAT 99,999     HEADING '02'<br />
COLUMN H03   FORMAT 99,999     HEADING '03'<br />
COLUMN H04   FORMAT 99,999     HEADING '04'<br />
COLUMN H05   FORMAT 99,999     HEADING '05'<br />
COLUMN H06   FORMAT 99,999     HEADING '06'<br />
COLUMN H07   FORMAT 99,999     HEADING '07'<br />
COLUMN H08   FORMAT 99,999     HEADING '08'<br />
COLUMN H09   FORMAT 99,999     HEADING '09'<br />
COLUMN H10   FORMAT 99,999     HEADING '10'<br />
COLUMN H11   FORMAT 99,999     HEADING '11'<br />
COLUMN H12   FORMAT 99,999     HEADING '12'<br />
COLUMN H13   FORMAT 99,999     HEADING '13'<br />
COLUMN H14   FORMAT 99,999     HEADING '14'<br />
COLUMN H15   FORMAT 99,999     HEADING '15'<br />
COLUMN H16   FORMAT 99,999     HEADING '16'<br />
COLUMN H17   FORMAT 99,999     HEADING '17'<br />
COLUMN H18   FORMAT 99,999     HEADING '18'<br />
COLUMN H19   FORMAT 99,999     HEADING '19'<br />
COLUMN H20   FORMAT 99,999     HEADING '20'<br />
COLUMN H21   FORMAT 99,999     HEADING '21'<br />
COLUMN H22   FORMAT 99,999     HEADING '22'<br />
COLUMN H23   FORMAT 99,999     HEADING '23'<br />
COLUMN TOTAL FORMAT 999,999    HEADING 'Total'<br />
SELECT<br />
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                                                               DAY<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',ROUND(((blocks*block_size)/1024/1024)),0)) H00<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',ROUND(((blocks*block_size)/1024/1024)),0)) H01<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',ROUND(((blocks*block_size)/1024/1024)),0)) H02<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',ROUND(((blocks*block_size)/1024/1024)),0)) H03<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',ROUND(((blocks*block_size)/1024/1024)),0)) H04<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',ROUND(((blocks*block_size)/1024/1024)),0)) H05<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',ROUND(((blocks*block_size)/1024/1024)),0)) H06<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',ROUND(((blocks*block_size)/1024/1024)),0)) H07<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',ROUND(((blocks*block_size)/1024/1024)),0)) H08<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',ROUND(((blocks*block_size)/1024/1024)),0)) H09<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',ROUND(((blocks*block_size)/1024/1024)),0)) H10<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',ROUND(((blocks*block_size)/1024/1024)),0)) H11<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',ROUND(((blocks*block_size)/1024/1024)),0)) H12<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',ROUND(((blocks*block_size)/1024/1024)),0)) H13<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',ROUND(((blocks*block_size)/1024/1024)),0)) H14<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',ROUND(((blocks*block_size)/1024/1024)),0)) H15<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',ROUND(((blocks*block_size)/1024/1024)),0)) H16<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',ROUND(((blocks*block_size)/1024/1024)),0)) H17<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',ROUND(((blocks*block_size)/1024/1024)),0)) H18<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',ROUND(((blocks*block_size)/1024/1024)),0)) H19<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',ROUND(((blocks*block_size)/1024/1024)),0)) H20<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',ROUND(((blocks*block_size)/1024/1024)),0)) H21<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',ROUND(((blocks*block_size)/1024/1024)),0)) H22<br />
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',ROUND(((blocks*block_size)/1024/1024)),0)) H23<br />
  , ROUND(SUM((blocks*block_size)/1024/1024))                                                                                                           TOTAL<br />
FROM<br />
  v$archived_log  a<br />
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)<br />
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)<br />
/<br />