以下转自：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