Archivelog calendar
                                                 Last update (2012-06-17 22:03:21)
                                                                                                                   Date added (2008-02-13 15:00:37)

Summary
You need to know, especially in TByte systems, how much archives produced every day, how they are produced during the day hours and of course how many of them are deleted by RMAN backup.

With this script you have a per day and per hour calendar of archiving! It shows how many produced as a total every day, how much have already deleted from Unix and what the remaining is in GBytes.
SELECT * FROM (
SELECT A.*, B.SIZE_GB, DECODE(C.SIZE_GB, NULL, 0, C.SIZE_GB) DELETED_GB, B.SIZE_GB - DECODE(C.SIZE_GB, NULL, 0, C.SIZE_GB) REMAIN_GB 
FROM (SELECT * FROM (SELECT TO_DATE(b.date_time, 'DD/MM/YYYY') dt, TO_CHAR(TO_DATE(b.date_time, 'DD/MM/YYYY'), 'DAY') DAY, 
"00", "01",  "02", "03", "04", "05", "06", "07", "08", "09", 
"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", TOTAL 
FROM (SELECT  date_time, 
SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02", 
SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05", 
SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08", 
SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",
SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",
SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",
SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",
SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL  
FROM (SELECT TO_CHAR(first_time, 'DD/MM/YYYY') DATE_TIME,  
SUBSTR(TO_CHAR(first_time, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$LOG_HISTORY)
--where date_time = '06/12/2004' 
GROUP BY date_time) b)) A, 
(SELECT TO_CHAR(FIRST_TIME, 'DD/MM/YYYY') DATE_TIME, 
SUM(ROUND((blocks*block_size)/(1024*1024*1024),3)) SIZE_GB 
FROM v$archived_log GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM/YYYY')) B, 
(SELECT TO_CHAR(FIRST_TIME, 'DD/MM/YYYY') DATE_TIME, 
SUM(ROUND((blocks*block_size)/(1024*1024*1024),3)) SIZE_GB 
FROM v$archived_log WHERE DELETED = 'YES' GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM/YYYY')) C 
WHERE TO_CHAR(A.DT, 'DD/MM/YYYY') = B.DATE_TIME 
AND TO_CHAR(A.DT, 'DD/MM/YYYY') = c.DATE_TIME(+)
) ORDER BY dt DESC;
Output
DT DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 TOTAL SIZE_GB DELETED_GB REMAIN_GB
18/2/2008 MONDAY 6 4 2     2     3 1 3 5 3 2 18                   49 19,423 12,954 6,469
17/2/2008 SUNDAY 6 3 4     2     2   1 4 1 2 2     3     3   6 3 42 14,577 14,577 0
16/2/2008 SATURDAY 58 55 36 18 7 5 20 14 3 52 60 40 13 11 43 16 2 2   1 2   1 7 466 177,047 177,047 0
15/2/2008 FRIDAY 8 7 45 24 22 5 2 4 8 5 54 6 5 4 20 4 3 2 4 2 4 25 36 60 359 146,44 146,44 0
14/2/2008 THURSDAY 8 6 2 2 3 2 3 2 5 3 7 7 5 7 8 4 3 3 3 16 10 3 3 8 123 51,207 51,207 0
13/2/2008 WEDNESDAY 7 5 7 60 64 55 38 30 28 29 35 61 79 89 92 93 99 91 70 15 45 14 6 3 1115 416,01 416,01 0
12/2/2008 TUESDAY 56 54 38 6 7 4 2 15 16 11 10 12 9 4 21 5 5 3 2 2 16 12 7 4 321 132,257 132,257 0
11/2/2008 MONDAY 45 4 2   1 2   3 5 3 15 60 5 3 7 4 11 28 14 4 10 30 52 56 364 147,742 147,742 0
10/2/2008 SUNDAY 5 4 3 2   2     3 1 2 4 3 9 3 6   3     3   13 40 106 40,397 40,397 0
9/2/2008 SATURDAY 48 9 19 33 11 12   1 7 5 5 3 1 2 19   5 2 84 87 47 15 6 2 423 159,33 159,33 0
8/2/2008 FRIDAY 9 8 72 77 72 25 22 32 21 12 82 9 5 5 5 4 10 36 19 12 49 64 66 67 783 299,919 299,919 0
7/2/2008 THURSDAY 48 57 64 9 22 31   1 6 5 5 4 4 5 4 2 4 4 8 14 7 3 3 9 319 127,344 127,344 0
6/2/2008 WEDNESDAY 6 6 16 7 6 3 2 3 5 3 83 6 4 2 20 4 3 4 3 5 29 60 62 57 399 161,974 161,974 0
5/2/2008 TUESDAY 8 7 14 4 2 2   3 4 5 6 8 5 4 5 2 5 4 2 3 8 12 12 15 140 57,463 57,463 0
4/2/2008 MONDAY 37 16 2   1 2   1 5 3 7 8 5 5 3 3 4 4 6 13 6 3 4 8 146 61,009 61,009 0
3/2/2008 SUNDAY       2   2   1 2 1 1 5 2 11 18 1 2 2   3 2   6 2 63 28,999 28,999 0
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search