oracle 数据库表空间文件收缩

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