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
