数据库用久了难免会出现没有回收的空间，如果空间太大可使用以下方法进行回收。

查询用个表所占用的空间：

 

SELECTtablespace_name,100*(sum_max-sum_alloc+nvl(sum_free,0))/sum_max AS capa_per,(sum_max-sum_alloc+nvl(sum_free,0))/1024/1024  AS capa_free,(sum_alloc - nvl(sum_free,0))/1024/1024 as capa_used,sum_max/1024/1024 as capa_max,100*nvl(sum_free,0)/sum_alloc As per,nvl(sum_free,0)/1024/1024 as free,(sum_alloc - nvl(sum_free,0))/1024/1024 as used,sum_alloc/1024/1024 as maxFROM ( SELECT tablespace_name, sum(bytes) AS sum_alloc, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_maxFROM dba_data_filesGROUP BY tablespace_name),( SELECT tablespace_name AS fs_ts_name, sum(bytes) AS sum_freeFROM dba_free_spaceGROUP BY tablespace_name )WHERE tablespace_name = fs_ts_name(+)order by 2,3;

其中MAX为当前占用磁盘空间（单位MB），USED为数据实际所需空间，FREE是可被回收的空间。

 

假设SYSTEM表MAX为10240M，USED为600M，数据存放于D:\ORADATA\DBA目录下，用以下命令将SYSTEM表空间缩小到610M：

 

alter database datafile  'D:\ORADATA\DBA\SYSTEM01.DBF' resize 610m;

其他表空间(如UNDOTBS1、INDX等)方法一样。