归档日志大小（通用）
无论归档日志放在文件系统还是ASM磁盘上，都可以通过如下方式查询：
1-- 按照天数计算
2SELECT to_char(FIRST_TIME,'YYYY-MM-DD') MD,
3 ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
4FROM v$archived_log a
5WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 30
6-- and a.name is not null
7group by to_char(FIRST_TIME,'YYYY-MM-DD')
8order by to_char(FIRST_TIME,'YYYY-MM-DD');
9
10
11
12-- 计算总大小
13SELECT ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
14FROM v$archived_log a
15WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 30
16-- and a.name is not null
17order by to_char(FIRST_TIME,'YYYY-MM-DD');
18
19
20
21
22-- 每天日志切换频率
23SELECT 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,
24 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
25 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
26 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
27 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
28 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
29 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
30 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
31 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
32 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
33 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
34 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
35 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
36 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
37 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
38 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
39 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
40 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
41 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
42 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
43 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
44 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
45 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
46 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
47 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
48 COUNT(*) TOTAL
49FROM gv$log_history a
50 WHERE first_time>=TO_CHAR(SYSDATE - 15)
51 group by a.THREAD#,
52 SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
53ORDER BY a.THREAD#,SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
54
55
56
57
58-- 闪回恢复区使用情况
59col name format a30
60SELECT A.NAME,
61 round(a.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
62 (a.space_used / 1024 / 1024) space_used_m,
63 round(a.space_used / a.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
64 round(a.space_reclaimable / 1024 / 1024, 2) space_reclaimable,
65 round(a.space_reclaimable / a.SPACE_LIMIT, 2) PERCENT_SPACE_RECLAIMABLE,
66 number_of_files
67 FROM v$recovery_file_dest A
68 WHERE a.SPACE_LIMIT <> 0
69UNION ALL
70SELECT b.FILE_TYPE,
71 (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
72 round(b.PERCENT_SPACE_USED * c.SPACE_LIMIT / 1024 / 1024 / 100, 2) space_used_m,
73 b.PERCENT_SPACE_USED PERCENT_SPACE_USED,
74 round(b.PERCENT_SPACE_RECLAIMABLE * c.SPACE_LIMIT / 1024 / 1024 / 100,
75 2) space_reclaimable,
76 (b.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
77 b.NUMBER_OF_FILES
78 FROM v$flash_recovery_area_usage b, v$recovery_file_dest c
79 WHERE c.SPACE_LIMIT <> 0
80UNION ALL
81SELECT bb.FILENAME || '---' || bb.STATUS,
82 (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
83 (bb.BYTES / 1024 / 1024) space_used,
84 round(bb.BYTES * 100 / c.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
85 0,
86 0,
87 1
88 FROM v$block_change_tracking bb, v$recovery_file_dest c
89 WHERE c.SPACE_LIMIT <> 0;
归档日志存放在在文件系统
在文件系统上，进入归档目录后，可以直接用如下命令查询：
1du -sh ./*
2
归档日志在ASM磁盘示例
1[oracle@rac1 ~]$ sas
2
3SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 28 11:11:06 2022
4
5Copyright (c) 1982, 2013, Oracle. All rights reserved.
6
7
8Connected to:
9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
10With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
11Data Mining and Real Application Testing options
12
13SQL> SELECT ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
14 2 FROM v$archived_log a
15 3 WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 115
16 4 and a.name is not null
17 5 order by to_char(FIRST_TIME,'YYYY-MM-DD');
18
19 LOGSIZE_G
20----------
21 59
22
23SQL>
24SQL> SELECT to_char(FIRST_TIME,'YYYY-MM-DD') MD,
25 2 ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
26 3 FROM v$archived_log a
27 4 WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 115
28 5 and a.name is not null
29 6 group by to_char(FIRST_TIME,'YYYY-MM-DD')
30 7 order by to_char(FIRST_TIME,'YYYY-MM-DD');
31
32MD LOGSIZE_G
33---------- ----------
342022-11-18 6
352022-11-19 3
362022-11-20 9
372022-11-21 7
382022-11-22 6
392022-11-23 6
402022-11-24 7
412022-11-25 6
422022-11-26 4
432022-11-27 3
442022-11-28 3
45
4611 rows selected.
47
48SQL>
49SQL>
50SQL>
51SQL>
52SQL> SELECT a.THREAD#, SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
53 2 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
54 3 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
55 4 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
56 5 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
57 6 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
58 7 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
59 8 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
60 9 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
61 10 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
62 11 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
63 12 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
64 13 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
65 14 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
66 15 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
67 16 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
68 17 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
69 18 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
70 19 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
71 20 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
72 21 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
73 22 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
74 23 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
75 24 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
76 25 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
77 26 COUNT(*) TOTAL
78 27 FROM gv$log_history a
79 28 WHERE first_time>=TO_CHAR(SYSDATE - 15)
80 29 group by a.THREAD#,
81 30 SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
82 31 ORDER BY a.THREAD#,SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
83
84 THREAD# DAY H00 H01 H02 H03 H04 H05 H06 H07 H08 H09 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23 TOTAL
85---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
86 1 11/28 8 6 0 2 0 0 0 2 2 4 4 0 0 0 0 0 0 0 0 0 0 0 0 0 28
87 1 11/27 8 2 0 2 0 0 2 0 2 0 0 2 2 0 2 2 0 2 0 2 2 0 2 0 32
88 1 11/26 8 6 0 0 0 2 0 0 2 0 2 0 2 0 2 2 2 2 2 0 2 2 0 0 36
89 1 11/25 12 4 0 0 0 0 2 0 2 4 4 4 2 2 4 6 4 4 0 2 0 0 4 0 60
90 1 11/24 6 4 0 0 0 0 2 0 2 6 4 6 2 2 6 8 4 6 2 0 2 0 4 10 76
91 1 11/23 8 6 2 0 0 2 0 0 2 4 4 4 2 4 4 6 2 4 0 2 2 0 4 2 64
92 1 11/22 6 4 0 0 0 2 0 0 4 4 4 4 6 2 4 6 6 4 2 2 2 0 4 2 68
93 1 11/21 0 2 0 0 0 2 0 0 2 10 6 4 2 2 10 8 6 2 2 0 0 2 18 2 80
94 1 11/20 2 2 0 0 0 2 2 2 2 0 86 0 0 2 2 0 12 2 2 0 0 0 2 4 124
95 1 11/19 0 2 0 0 2 0 0 2 2 0 2 2 0 0 6 0 8 0 2 0 0 0 2 2 32
96 1 11/18 0 6 2 0 0 0 2 0 2 6 4 4 2 2 6 8 10 6 2 8 6 2 12 10 100
97 1 11/17 2 2 0 0 2 0 0 0 2 6 6 4 0 0 2 6 12 2 8 0 0 2 2 2 60
98 1 11/16 0 2 0 0 0 2 0 0 2 4 6 4 2 2 6 8 4 2 0 2 0 0 12 0 58
99 1 11/15 0 2 0 0 2 0 0 2 0 4 4 4 2 2 4 8 2 4 0 2 0 2 20 2 66
100 1 11/14 2 8 0 0 0 2 0 0 0 4 6 2 2 2 10 6 6 4 0 2 0 2 26 4 88
101 1 11/13 0 4 0 0 0 0 6 2 0 0 2 0 0 2 0 0 0 2 0 2 0 0 4 0 24
102 2 11/28 24 2 0 2 0 0 2 0 4 6 6 2 0 0 0 0 0 0 0 0 0 0 0 0 48
103 2 11/27 24 8 0 0 2 0 0 2 4 0 4 2 2 2 4 2 2 4 2 2 2 2 2 0 72
104 2 11/26 22 2 0 2 0 2 0 2 2 2 6 2 2 0 8 2 2 4 6 0 2 2 2 0 72
105 2 11/25 40 8 0 2 0 0 2 0 2 6 6 6 4 2 6 6 6 6 4 2 0 2 10 2 122
106 2 11/24 22 8 0 0 0 2 0 2 0 6 6 6 2 4 6 6 6 6 2 2 2 2 14 18 122
107 2 11/23 24 2 0 0 0 2 0 2 0 6 6 4 2 4 6 4 6 4 2 2 0 2 14 2 94
108 2 11/22 22 8 0 0 2 0 0 2 4 6 6 4 2 4 6 6 6 4 2 2 4 2 12 0 104
109 2 11/21 0 8 0 2 0 0 0 2 0 24 10 6 2 2 26 10 8 4 4 0 2 2 6 4 122
110 2 11/20 0 6 0 2 0 0 0 4 0 0 28 0 2 0 0 2 34 10 0 2 0 2 0 2 94
111 2 11/19 0 8 0 0 2 0 2 2 0 2 2 0 2 0 2 2 20 2 0 2 0 2 0 2 52
112 2 11/18 0 4 0 0 2 0 2 0 2 4 6 6 2 4 8 10 28 6 4 18 22 4 4 14 150
113 2 11/17 2 8 2 0 0 2 0 0 2 6 8 4 2 0 2 6 6 4 22 0 2 0 6 2 86
114 2 11/16 0 8 0 0 2 0 0 2 2 4 4 4 2 4 10 6 4 4 0 2 0 2 4 2 66
115 2 11/15 2 6 0 2 0 0 2 0 0 8 6 4 2 4 4 8 4 4 2 0 2 2 20 2 84
116 2 11/14 2 2 0 0 2 0 0 2 0 4 6 4 2 8 14 10 4 4 2 0 0 2 18 10 96
117 2 11/13 0 8 0 0 2 0 2 0 0 2 0 0 0 2 0 2 0 2 0 2 0 0 2 0 24
118
11932 rows selected.
120
121SQL> SELECT A.NAME,
122 2 round(a.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
123 3 (a.space_used / 1024 / 1024) space_used_m,
124 4 round(a.space_used / a.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
125 5 round(a.space_reclaimable / 1024 / 1024, 2) space_reclaimable,
126 6 round(a.space_reclaimable / a.SPACE_LIMIT, 2) PERCENT_SPACE_RECLAIMABLE,
127 7 number_of_files
128 8 FROM v$recovery_file_dest A
129 9 WHERE a.SPACE_LIMIT <> 0
130 10 UNION ALL
131 11 SELECT b.FILE_TYPE,
132 12 (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
133 13 round(b.PERCENT_SPACE_USED * c.SPACE_LIMIT / 1024 / 1024 / 100, 2) space_used_m,
134 14 b.PERCENT_SPACE_USED PERCENT_SPACE_USED,
135 15 round(b.PERCENT_SPACE_RECLAIMABLE * c.SPACE_LIMIT / 1024 / 1024 / 100,
136 16 2) space_reclaimable,
137 17 (b.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
138 18 b.NUMBER_OF_FILES
139 19 FROM v$flash_recovery_area_usage b, v$recovery_file_dest c
140 20 WHERE c.SPACE_LIMIT <> 0
141 21 UNION ALL
142 22 SELECT bb.FILENAME || '---' || bb.STATUS,
143 23 (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
144 24 (bb.BYTES / 1024 / 1024) space_used,
145 25 round(bb.BYTES * 100 / c.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
146 26 0,
147 27 0,
148 28 1
149 29 FROM v$block_change_tracking bb, v$recovery_file_dest c
150 30 WHERE c.SPACE_LIMIT <> 0;
151
152NAME SPACE_LIMIT_M SPACE_USED_M PERCENT_SPACE_USED SPACE_RECLAIMABLE PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
153------------------------------ ------------- ------------ ------------------ ----------------- ------------------------- ---------------
154+DATA 819200 65389 .08 0 0 851
155CONTROL FILE 819200 0 0 0 0 1
156REDO LOG 819200 4177.92 .51 0 0 14
157ARCHIVED LOG 819200 61112.32 7.46 0 0 836
158BACKUP PIECE 819200 0 0 0 0 0
159IMAGE COPY 819200 0 0 0 0 0
160FLASHBACK LOG 819200 0 0 0 0 0
161FOREIGN ARCHIVED LOG 819200 0 0 0 0 0
162---DISABLED 819200 0 0 1
163
1649 rows selected.
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
