Difference between revisions of "Calculate the size of archive log files each day"

From Ittichai Chammavanijakul's Wiki
Jump to navigation Jump to search
 
(13 intermediate revisions by the same user not shown)
Line 17: Line 17:
 
:
 
:
 
</pre>
 
</pre>
 +
 
* Size of the archive log files each hour.
 
* Size of the archive log files each hour.
 
<pre>
 
<pre>
−
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH AM';
+
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';
  
 
Session altered.
 
Session altered.
Line 29: Line 30:
 
       ORDER BY 1;
 
       ORDER BY 1;
  
−
ARCHIVED_DATE_HOUR SIZE_IN_MB
+
ARCHIVED_DATE SIZE_IN_MB
−
------------------ ----------
+
------------- ----------
−
26-Jan-11 0:00:00  13304.7856
+
2011-01-26 00 13304.7856
−
26-Jan-11 1:00:00  7262.39208
+
2011-01-26 01 7262.39209
−
26-Jan-11 2:00:00  4444.78613
+
2011-01-26 02 4444.78613
−
26-Jan-11 3:00:00  7229.76367
+
2011-01-26 03 7229.76367
−
26-Jan-11 4:00:00  9936.16552
+
2011-01-26 04 9936.16553
−
26-Jan-11 5:00:00  6468.24316
+
2011-01-26 05 6468.24316
−
26-Jan-11 6:00:00 8904.08740
+
2011-01-26 06 8904.0874
−
26-Jan-11 7:00:00  11593.7358
+
2011-01-26 07 11593.7358
−
26-Jan-11 8:00:00  20205.6821
+
2011-01-26 08 20205.6821
−
26-Jan-11 9:00:00  20117.7275
+
2011-01-26 09 20117.7275
−
26-Jan-11 10:00:00 18498.4887
+
2011-01-26 10 18498.4888
−
26-Jan-11 11:00:00 17875.3457
+
2011-01-26 11 17875.3457
−
26-Jan-11 12:00:00 7012.13427
+
2011-01-26 12 7012.13428
−
26-Jan-11 1:00:00  8923.90136
+
2011-01-26 13 8923.90137
−
26-Jan-11 2:00:00  13567.7656
+
2011-01-26 14 13567.7656
−
26-Jan-11 3:00:00  5032.42382
+
2011-01-26 15 5032.42383
 +
2011-01-26 16 17356.7549
 +
2011-01-26 17 26535.3154
 +
2011-01-26 18 15555.6626
 +
2011-01-26 19 8450.63184
 +
2011-01-26 20 7845.36865
 +
2011-01-26 21 10421.9067
 +
2011-01-26 22 11770.5005
 +
2011-01-26 23 15328.6797
 
:
 
:
 
:
 
:
Line 54: Line 63:
 
SQL> SELECT
 
SQL> SELECT
 
             TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
 
             TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
−
                        THREAD#,
+
            THREAD#,
 
             SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
 
             SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
 
       FROM V$ARCHIVED_LOG
 
       FROM V$ARCHIVED_LOG
Line 81: Line 90:
 
:
 
:
 
</pre>
 
</pre>
 +
== See Also ==
 +
* [[Log Switch History Matrix]]
 +
 +
[[Category:Oracle_Scripts]]

Latest revision as of 17:41, 16 March 2011

  • Size of the archive log files each day
SQL> SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
           SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
      FROM V$ARCHIVED_LOG
     GROUP BY TRUNC(COMPLETION_TIME)
     ORDER BY 1;
	
ARCHIVED_DATE SIZE_IN_MB
------------- ----------
26-Jan-11     293642.249
27-Jan-11     260688.605
28-Jan-11     301309.071
29-Jan-11     278268.036
30-Jan-11     88501.3779
:
:
  • Size of the archive log files each hour.
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';

Session altered.

SQL> SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
       FROM V$ARCHIVED_LOG
      GROUP BY TRUNC(COMPLETION_TIME, 'HH')
      ORDER BY 1;

ARCHIVED_DATE SIZE_IN_MB
------------- ----------
2011-01-26 00 13304.7856
2011-01-26 01 7262.39209
2011-01-26 02 4444.78613
2011-01-26 03 7229.76367
2011-01-26 04 9936.16553
2011-01-26 05 6468.24316
2011-01-26 06  8904.0874
2011-01-26 07 11593.7358
2011-01-26 08 20205.6821
2011-01-26 09 20117.7275
2011-01-26 10 18498.4888
2011-01-26 11 17875.3457
2011-01-26 12 7012.13428
2011-01-26 13 8923.90137
2011-01-26 14 13567.7656
2011-01-26 15 5032.42383
2011-01-26 16 17356.7549
2011-01-26 17 26535.3154
2011-01-26 18 15555.6626
2011-01-26 19 8450.63184
2011-01-26 20 7845.36865
2011-01-26 21 10421.9067
2011-01-26 22 11770.5005
2011-01-26 23 15328.6797
:
:
  • Size of the archive log files each day per instance.
SQL> SELECT
            TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
            THREAD#,
            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
       FROM V$ARCHIVED_LOG
      GROUP BY TRUNC(COMPLETION_TIME), THREAD#
      ORDER BY 1, 2;

ARCHIVED_    THREAD# SIZE_IN_MB
--------- ---------- ----------
26-JAN-11          1 148411.591
26-JAN-11          2 145230.658
27-JAN-11          1 171689.525
27-JAN-11          2 88999.0806
28-JAN-11          1 168748.924
28-JAN-11          2 132560.147
29-JAN-11          1 153350.945
29-JAN-11          2 124917.091
30-JAN-11          1 39691.7251
30-JAN-11          2 48809.6528
31-JAN-11          1 125617.452
31-JAN-11          2 119977.382
01-FEB-11          1  46182.313
01-FEB-11          2 235871.665
02-FEB-11          1 39296.0962
02-FEB-11          2  84730.332
:
:

See Also

Retrieved from "https://ittichaicham.com/wiki/index.php?title=Calculate_the_size_of_archive_log_files_each_day&oldid=308"