Archive Log Statastics
select * from V$LOG_HISTORY
select * from V$ARCHIVE_DEST
select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;
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;
select sum((blocks*block_size)/(1024*1024))SIZEinMB,sequence#,name
from v$archived_log
group by sequence#,name
order by sequence#
SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,(select avg(bytes) log_size from v$log)
GROUP BY trunc(first_time),log_size
/
-- Size of the archive log files each hour
alter session set nls_date_format = 'YYYY-MM-DD HH24';
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;
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;
-- per day the volume in MBytes of archived logs generated
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
-- display the number of archived logs generated per hour per day:
---number of archived logs generated per hour per day
SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
"10-11",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
-- Combination of these scripts is:
SELECT LOG_HISTORY.*,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
"10-11",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (
DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) LOG_HISTORY,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE LOG_HISTORY.DAY = SUM_ARCH.DAY AND SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (LOG_HISTORY.DAY, 'DD/MM/YYYY');
