Trend Oracle Archive Graph : How much archive created y’day or last week

Often you want to show nice graphs/pictures to please your Colleagues. Here is an Excel graph I created to record Archive growth.

The following query shows a count and size of the redo log files by day:
– Daily Count and Size of Redo Log Space (Single Instance)
–

SELECT A.*,

Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb

FROM

(

SELECT

To_Char(First_Time,'YYYY-MM-DD') DAY,

Count(1) Count#,

Min(RECID) Min#,

Max(RECID) Max#

FROM

v$log_history

GROUP BY

To_Char(First_Time,'YYYY-MM-DD')

ORDER

BY 1 DESC

) A,

(

SELECT

Avg(BYTES) AVG#,

Count(1) Count#,

Max(BYTES) Max_Bytes,

Min(BYTES) Min_Bytes

FROM

v$log

) B

;

This v$log_history script comes from Ilya Petrenko

Output of the script : Delete columns Min(RECID) Min#,   Max(RECID) Max#

DAY	LOG COUNT	DAILY_AVG_MB
02/07/2010	98	4480
01/07/2010	126	5760
30/06/2010	54	2469
29/06/2010	28	1280
28/06/2010	37	1691
27/06/2010	14	640
26/06/2010	14	640
25/06/2010	14	640
24/06/2010	19	869
23/06/2010	14	640

Copy this table into Excel & click on Chart Wizard. Select X,Y co-ordinates and you will get a nice picture like this