本文介绍了一种查询Oracle数据库中数据文件使用情况的方法，并提供了具体的SQL语句来展示如何获取文件的详细信息，包括文件大小、已用空间、可用空间等，并给出了通过调整数据文件大小进行空间回收的具体建议。
1. 查看数据文件使用情况
select /*+ ordered use_hash(a,b,c) */
a.file_id,a.file_name,a.filesize, b.freesize,
(a.filesize-b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize-b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
from
(
select file_id,file_name,round(bytes/1024/1024)
filesize from dba_data_files
) a,
(
select file_id,round(sum(dfs.bytes)/1024/1024)
freesize from dba_free_space dfs
group by file_id
) b,
(
select file_id,round(max(block_id)*8/1024)
HWMsize from dba_extents
group by file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
order by unsedsize_belowhwm desc
结果说明：
File_id ： 文件编号
File_name: 文件名称
File_size: 数据文件占用磁盘空间大小
Freesize：文件中被标记为free的空间大小
Usedsize: 使用的空间大小。
Hwmsize： 已经分配出去的空间大小，如果希望通过alter database datafile … resize integerM回收空间，将需要这个值作为参考，不能回收到这个值之下，否则会报错。
Freee_belowhwm_size： 在HWM（高水位标记线之下的空闲空间数），这个是理论上的可以回收的空间大小。
Curr_can_shrink： 这个是实际大小与HWM标记之间的差，就是还没有分配出去的空间大小。
|
file_id |
file_name |
filesize |
freesize |
usedsize |
hwmsize |
free_belowhwm_size |
curr_can_shrink |
|
11 |
/oradata/ODSD01.dbf |
2048 |
1908 |
140 |
2048 |
1908 |
0 |
|
12 |
/oradata/ODSD02.dbf |
2048 |
1897 |
151 |
2048 |
1897 |
0 |
|
20 |
/oradata/ODSD10.dbf |
2048 |
1897 |
151 |
2048 |
1897 |
0 |
|
16 |
/oradata/ODSD06.dbf |
2048 |
1889 |
159 |
2048 |
1889 |
0 |
|
15 |
/oradata/ODSD05.dbf |
2048 |
1888 |
160 |
2048 |
1888 |
0 |
|
19 |
/oradata/ODSD09.dbf |
2048 |
1885 |
163 |
2048 |
1885 |
0 |
|
13 |
/oradata/ODSD03.dbf |
2048 |
1884 |
164 |
2048 |
1884 |
0 |
|
17 |
/oradata/ODSD07.dbf |
2048 |
1884 |
164 |
2048 |
1884 |
0 |
|
14 |
/oradata/ODSD04.dbf |
2048 |
1813 |
235 |
2041 |
1806 |
7 |
|
34 |
/oradata/DWD01.dbf |
4000 |
3701 |
299 |
2088 |
1789 |
1912 |
|
51 |
/oradata/ODSD11.dbf |
2048 |
1963 |
85 |
1584 |
1499 |
464 |
|
21 |
/oradata/ODSI01.dbf |
2048 |
1913 |
135 |
1617 |
1482 |
431 |
|
25 |
/oradata/ODSI05.dbf |
2048 |
1910 |
138 |
1607 |
1469 |
441 |
|
22 |
/oradata/ODSI02.dbf |
2048 |
1903 |
145 |
1606 |
1461 |
442 |
|
24 |
/oradata/ODSI04.dbf |
2048 |
1909 |
139 |
1592 |
1453 |
456 |
|
23 |
/oradata/ODSI03.dbf |
2048 |
1892 |
156 |
1603 |
1447 |
445 |
|
48 |
/oradata/ODSI06.dbf |
2048 |
1925 |
123 |
1559 |
1436 |
489 |
|
30 |
/oradata/TODSD05.dbf |
2048 |
1804 |
244 |
1315 |
1071 |
733 |
|
18 |
/oradata/ODSD08.dbf |
2048 |
1881 |
167 |
1225 |
1058 |
823 |
|
27 |
/oradata/TODSD02.dbf |
2048 |
1818 |
230 |
1244 |
1014 |
804 |
|
31 |
/oradata/TODSI01.dbf |
2048 |
1977 |
71 |
936 |
865 |
1112 |
|
35 |
/oradata/DWI01.dbf |
2048 |
1973 |
75 |
936 |
861 |
1112 |
|
32 |
/oradata/TODSI02.dbf |
2048 |
1969 |
79 |
867 |
788 |
1181 |
|
43 |
/oradata/DWI03.dbf |
2048 |
1975 |
73 |
802 |
729 |
1246 |
|
42 |
/oradata/DWI02.dbf |
2048 |
1983 |
65 |
755 |
690 |
1293 |
|
39 |
/oradata/TODSI04.dbf |
2048 |
1971 |
77 |
680 |
603 |
1368 |
|
26 |
/oradata/TODSD01.dbf |
2048 |
1819 |
229 |
830 |
601 |
1218 |
|
40 |
/oradata/TODSI05.dbf |
2048 |
1976 |
72 |
609 |
537 |
1439 |
|
28 |
/oradata/TODSD03.dbf |
2048 |
1793 |
255 |
702 |
447 |
1346 |
|
37 |
/oradata/TODSI03.dbf |
2048 |
1946 |
102 |
450 |
348 |
1598 |
|
29 |
/oradata/TODSD04.dbf |
2048 |
1793 |
255 |
485 |
230 |
1563 |
|
33 |
/oradata/CTL01.dbf |
500 |
494 |
6 |
21 |
15 |
479 |
|
10 |
/oradata/xdb01.dbf |
47 |
3 |
44 |
46 |
2 |
1 |
|
1 |
/oradata/system01.dbf |
1040 |
6 |
1034 |
1034 |
0 |
6 |
|
3 |
/oradata/cwmlite01.dbf |
20 |
2 |
18 |
18 |
0 |
2 |
|
4 |
/oradata/drsys01.dbf |
20 |
10 |
10 |
10 |
0 |
10 |
|
36 |
/oradata/OD01.dbf |
500 |
407 |
93 |
93 |
0 |
407 |
|
5 |
/oradata/example01.dbf |
139 |
0 |
139 |
139 |
0 |
0 |
|
54 |
/oradata/TCLKING.dbf |
5 |
0 |
5 |
5 |
0 |
0 |
|
56 |
/oradata/undotbs03.dbf |
1000 |
996 |
4 |
4 |
0 |
996 |
|
55 |
/oradata/HWM01.dbf |
5000 |
4963 |
37 |
37 |
0 |
4963 |
|
49 |
/oradata/DP23.dbf |
10 |
7 |
3 |
3 |
0 |
7 |
|
7 |
/oradata/odm01.dbf |
20 |
11 |
9 |
9 |
0 |
11 |
|
9 |
/oradata/users01.dbf |
83 |
0 |
83 |
82 |
-1 |
1 |
|
46 |
/oradata/RPTI01.dbf |
1024 |
802 |
222 |
221 |
-1 |
803 |
|
45 |
/oradata/RPTD01.dbf |
1024 |
923 |
101 |
100 |
-1 |
924 |
|
38 |
/oradata/FBI.dbf |
200 |
79 |
121 |
120 |
-1 |
80 |
select /*+ ordered use_hash(a,c) */
'alter database datafile '''||a.file_name||''' resize '||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',
a.filesize,
c.hwmsize
from
(
select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files
) a,
(
select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents
group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 100
|
收缩语句 |
文件大小 |
收缩目标 |
|
alter database datafile '/oradata/HWM02.dbf' resize 2671M; |
5000 |
1989 |
|
alter database datafile '/oradata/ODSD01.dbf' resize 598M; |
2048 |
136 |
|
alter database datafile '/oradata/ODSD02.dbf' resize 592M; |
2048 |
128 |
|
alter database datafile '/oradata/ODSD03.dbf' resize 591M; |
2048 |
127 |
|
alter database datafile '/oradata/ODSD04.dbf' resize 742M; |
2048 |
316 |
|
alter database datafile '/oradata/ODSD05.dbf' resize 594M; |
2048 |
130 |
|
alter database datafile '/oradata/ODSD06.dbf' resize 597M; |
2048 |
134 |
|
alter database datafile '/oradata/ODSD07.dbf' resize 598M; |
2048 |
135 |
|
alter database datafile '/oradata/ODSD08.dbf' resize 472M; |
1470 |
122 |
|
alter database datafile '/oradata/ODSD09.dbf' resize 587M; |
2048 |
122 |
|
alter database datafile '/oradata/ODSD10.dbf' resize 595M; |
2048 |
132 |
|
alter database datafile '/oradata/ODSI01.dbf' resize 507M; |
1783 |
88 |
|
alter database datafile '/oradata/ODSI02.dbf' resize 505M; |
1774 |
88 |
|
alter database datafile '/oradata/ODSI03.dbf' resize 529M; |
1772 |
118 |
|
alter database datafile '/oradata/ODSI04.dbf' resize 517M; |
1763 |
105 |
|
alter database datafile '/oradata/ODSI05.dbf' resize 525M; |
1775 |
113 |
|
alter database datafile '/oradata/TODSD01.dbf' resize 497M; |
1154 |
233 |
|
alter database datafile '/oradata/TODSD02.dbf' resize 561M; |
1485 |
230 |
|
alter database datafile '/oradata/TODSD03.dbf' resize 465M; |
1051 |
218 |
|
alter database datafile '/oradata/TODSD04.dbf' resize 431M; |
878 |
219 |
|
alter database datafile '/oradata/TODSD05.dbf' resize 598M; |
1542 |
262 |
|
alter database datafile '/oradata/TODSI01.dbf' resize 385M; |
1238 |
72 |
|
alter database datafile '/oradata/TODSI02.dbf' resize 365M; |
1183 |
60 |
|
alter database datafile '/oradata/CTL01.dbf' resize 146M; |
197 |
33 |
|
alter database datafile '/oradata/DWD01.dbf' resize 770M; |
2550 |
225 |
|
alter database datafile '/oradata/DWI01.dbf' resize 386M; |
1238 |
73 |
|
alter database datafile '/oradata/OD01.dbf' resize 152M; |
254 |
27 |
|
alter database datafile '/oradata/TODSI03.dbf' resize 288M; |
850 |
48 |
|
alter database datafile '/oradata/TODSI04.dbf' resize 324M; |
1034 |
46 |
|
alter database datafile '/oradata/TODSI05.dbf' resize 343M; |
977 |
84 |
|
alter database datafile '/oradata/DWI02.dbf' resize 356M; |
1094 |
72 |
|
alter database datafile '/oradata/DWI03.dbf' resize 366M; |
1131 |
75 |
|
alter database datafile '/oradata/RPTD01.dbf' resize 231M; |
365 |
98 |
|
alter database datafile '/oradata/RPTI01.dbf' resize 300M; |
462 |
159 |
|
alter database datafile '/oradata/ODSI06.dbf' resize 505M; |
1737 |
97 |
|
alter database datafile '/oradata/ODSD11.dbf' resize 535M; |
1757 |
129 |
|
alter database datafile '/oradata/undotbs03.dbf' resize 176M; |
283 |
49 |
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
