Difference between pages "Check Database Size" and "Check Generated Archivelog Sizes"
(Difference between pages)
Jump to navigation
Jump to search
(Created page with "<blockquote><pre> SELECT sum(megabytes) FROM ( SELECT sum(bytes)/1024/1024 megabytes FROM dba_temp_files UNION SELECT sum(bytes)/1024/1024 megabytes FROM...") |
(Created page with "Script may or may not work for everyone. The difficulty with this one is that it has to get the archivelog sizes from v$archived_log which could contain duplicate rows as ind...") |
||
| Line 1: | Line 1: | ||
| − | + | Script may or may not work for everyone. The difficulty with this one is that it has to get the archivelog sizes from v$archived_log which could contain duplicate rows as indicated in Oracle documentation .... | |
| − | + | ||
| − | + | "If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name" | |
| − | + | ||
| − | + | To counter this, I was able to simply filter out by the name field, which uniquely contained the orignal archivelog names ending with .arc. | |
| − | + | ||
| − | + | <pre> | |
| − | + | select to_char(b.first_time,'yyyy-mm-dd') DAY | |
| − | </pre | + | , count(*) NO_LOGS |
| + | , sum(BLOCKS * BLOCK_SIZE)/1024/1024 GB | ||
| + | from v$archived_log a | ||
| + | , v$log_history b | ||
| + | where a.sequence# = b.sequence# | ||
| + | and a.thread# = b.thread# | ||
| + | and a.first_change# = b.first_change# | ||
| + | and a.name like '%.arc%' | ||
| + | group by to_char(b.first_time,'yyyy-mm-dd'); | ||
| + | </pre> | ||
Latest revision as of 22:55, 8 November 2019
Script may or may not work for everyone. The difficulty with this one is that it has to get the archivelog sizes from v$archived_log which could contain duplicate rows as indicated in Oracle documentation ....
"If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name"
To counter this, I was able to simply filter out by the name field, which uniquely contained the orignal archivelog names ending with .arc.
select to_char(b.first_time,'yyyy-mm-dd') DAY
, count(*) NO_LOGS
, sum(BLOCKS * BLOCK_SIZE)/1024/1024 GB
from v$archived_log a
, v$log_history b
where a.sequence# = b.sequence#
and a.thread# = b.thread#
and a.first_change# = b.first_change#
and a.name like '%.arc%'
group by to_char(b.first_time,'yyyy-mm-dd');
