archive log history per day of last 30 days

 

SQL> 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
 ;

output is

 

DAY COUNT# MIN# MAX# DAILY_AVG_MB
11/14/2012 13 17377 17390 2600
11/13/2012 17 17360 17378 3400
11/12/2012 20 17340 17361 4000
11/11/2012 18 17323 17341 3600
11/10/2012 14 17309 17322 2800
11/9/2012 12 17297 17308 2400
11/8/2012 19 17278 17296 3800
11/7/2012 19 17259 17277 3800
11/6/2012 17 17242 17258 3400
11/5/2012 22 17220 17241 4400
11/4/2012 17 17202 17219 3400
11/3/2012 20 17183 17203 4000
11/2/2012 13 17169 17182 2600
11/1/2012 21 17148 17170 4200
10/31/2012 20 17129 17149 4000
10/30/2012 19 17109 17128 3800
10/29/2012 18 17091 17110 3600
10/28/2012 17 17075 17092 3400
10/27/2012 14 17061 17074 2800
10/26/2012 14 17047 17060 2800
10/25/2012 14 17033 17046 2800
10/24/2012 22 17011 17032 4400
10/23/2012 199 16810 17010 39800
10/22/2012 125 16686 16815 25000
10/21/2012 65 16621 16687 13000
10/20/2012 17 16604 16623 3400
10/19/2012 13 16592 16605 2600
10/18/2012 19 16572 16591 3800
10/17/2012 21 16552 16573 4200
10/16/2012 27 16524 16551 5400
10/15/2012 21 16503 16525 4200
10/14/2012 20 16483 16504 4000
10/13/2012 17 16467 16484 3400
10/12/2012 13 16454 16466 2600
10/11/2012 19 16435 16453 3800
10/10/2012 24 16411 16434 4800
10/9/2012 22 16389 16410 4400
10/8/2012 21 16367 16388 4200
10/7/2012 16 16351 16368 3200
10/6/2012 11 16340 16352 2200
10/5/2012 118 16223 16341 23600
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in scripts. Bookmark the permalink.

2 Responses to archive log history per day of last 30 days

  1. Santhoshkumar says:

    Hi Sher Khan,

    This post is really good……

    Thanks for sharing the knowledge keep on sharing…………..

    Thanks and Regards,
    T.Santhoshkumar

  2. Zakir Hossain Pervez, DBA, Eastern Bank Ltd says:

    Really, this is good.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s