合 Oracle查询归档日志的产生情况（每天增量大小）
👉 本文共约165个字，系统预计阅读时间或需1分钟。
归档日志大小（通用）
无论归档日志放在文件系统还是ASM磁盘上，都可以通过如下方式查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | -- 按照天数计算 SELECT to_char(FIRST_TIME,'YYYY-MM-DD') MD, ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G FROM v$archived_log a WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 30 -- and a.name is not null group by to_char(FIRST_TIME,'YYYY-MM-DD') order by to_char(FIRST_TIME,'YYYY-MM-DD'); -- 计算总大小 SELECT ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G FROM v$archived_log a WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 30 -- and a.name is not null order by to_char(FIRST_TIME,'YYYY-MM-DD'); -- 每天日志切换频率 SELECT a.THREAD#, '<div align="center"><font color="#336699"><b>' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || '</b></font></div>' Day, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, COUNT(*) TOTAL FROM gv$log_history a WHERE first_time>=TO_CHAR(SYSDATE - 15) group by a.THREAD#, SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ORDER BY a.THREAD#,SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC; -- 闪回恢复区使用情况 col name format a30 SELECT A.NAME, round(a.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m, (a.space_used / 1024 / 1024) space_used_m, round(a.space_used / a.SPACE_LIMIT, 2) PERCENT_SPACE_USED, round(a.space_reclaimable / 1024 / 1024, 2) space_reclaimable, round(a.space_reclaimable / a.SPACE_LIMIT, 2) PERCENT_SPACE_RECLAIMABLE, number_of_files FROM v$recovery_file_dest A WHERE a.SPACE_LIMIT <> 0 UNION ALL SELECT b.FILE_TYPE, (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m, round(b.PERCENT_SPACE_USED * c.SPACE_LIMIT / 1024 / 1024 / 100, 2) space_used_m, b.PERCENT_SPACE_USED PERCENT_SPACE_USED, round(b.PERCENT_SPACE_RECLAIMABLE * c.SPACE_LIMIT / 1024 / 1024 / 100, 2) space_reclaimable, (b.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE, b.NUMBER_OF_FILES FROM v$flash_recovery_area_usage b, v$recovery_file_dest c WHERE c.SPACE_LIMIT <> 0 UNION ALL SELECT bb.FILENAME || '---' || bb.STATUS, (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m, (bb.BYTES / 1024 / 1024) space_used, round(bb.BYTES * 100 / c.SPACE_LIMIT, 2) PERCENT_SPACE_USED, 0, 0, 1 FROM v$block_change_tracking bb, v$recovery_file_dest c WHERE c.SPACE_LIMIT <> 0; |
归档日志存放在在文件系统
在文件系统上，进入归档目录后，可以直接用如下命令查询：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
