以下查询中COMPLETION_TIME列可以换成FIRST_TIME或NEXT_TIME
ORACLE RAC按实例统计每天归档日志大小:
SELECT TRUNC (COMPLETION_TIME) ARCHIVED_DATE,
THREAD#,
ROUND (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024, 0) SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC (COMPLETION_TIME), THREAD#
ORDER BY 1, 2
ARCHIVED_ THREAD# SIZE_IN_MB
--------- ---------- ----------
04-十一月 1 2485
-19
04-十一月 2 2100
-19
05-十一月 1 651
-19
05-十一月 2 709
-19
06-十一月 1 556
-19
06-十一月 2 692
-19
07-十一月 1 719
-19
采集到MySQL在MySQL中统计语句如下:
SELECT device_id, `thread#`,date_format (first_TIME, '%Y-%m-%d') ARCHIVED_DATE,
format(SUM(blocks*block_size)/1024/1024,0) SIZE_IN_MB
FROM archived_log
GROUP BY device_id,`thread#`, date_format (first_TIME, '%Y-%m-%d')
ORDER BY 1,2,3;