收缩表空间大小

用户所关联的表都删除了，但是表空间不能自己释放。找到了下面一条SQL语句，它的作用是查询（拼接）出缩小表空间文件(datafile)的语句，默认是计算所有的表空间文件，给出可以释放的大小。根据它给出的结果，执行类似“ alter database datafile 'E:\ORACLE\NNC_DATA03.DBF' resize 1208M;”的语句，就可以立竿见影的看到压缩表空间的效果了。

SELECT
	A .file#,
	A . NAME,
	A .bytes / 1024 / 1024 CurrentMB,
	CEIL (HWM * A .block_size) / 1024 / 1024 ResizeTo,
	(A .bytes - HWM * A .block_size) / 1024 / 1024 ReleaseMB,
	'alter database datafile ''' || A . NAME || ''' resize ' || CEIL (HWM * A .block_size / 1024 / 1024) || 'M;' ResizeCMD
FROM
	v$datafile A,
	(
		SELECT
			file_id,
			MAX (block_id + BLOCKS - 1) HWM
		FROM
			dba_extents
		GROUP BY
			file_id
	) b
WHERE
	A .file# = b.file_id (+)
AND (A .bytes - HWM * block_size) > 0
ORDER BY
	5

在Oracle中，经常有这样的情况，由于误操作，使某个表空间过大。delete 方法不会清除高水位线，用了truncate之后，虽然高水位线已经清除，但是扩充的表空间并没有缩小，所以应该用下面的方法进行缩小：
1.查找表空间对应的数据文件id，以USERS表空间为例：
select FILE_ID from dba_data_files where tablespace_name ='USERS'
2.允许表空间进行收缩（对表空间进行融合）：
alter tablespace users coalesce;
3.查询表空间中表对应的大小，好确定对哪张表进行操作：
select * from dba_segments where tablespace_name='USERS' and segment_type='TABLE'
4.允许表进行行移动（某些表不能进行truncate，只能delete）
alter table test row movement;
5.对表进行高水位线回收：
alter table test shrink space;
6.最后，对第一步查出的数据文件id进行压缩（resize），大小一定要大于已用大小：
alter database datafile 4 resize 2000M;
经过上述操作，扩大的表空间就可以收缩回正常尺寸了，亲测可用！