以下转自：http://blog.csdn.net/47522341/article/details/3865946 作者：47522341
收缩空闲表空间
首先，如果没有分配的空间不足100M，则不考虑收缩。
收缩目标：当前数据文件大小 － （没分配空间－ 100M）×0.8
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 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22207394/viewspace-1178377/ ，如需转载，请注明出处，否则将追究法律责任。
