oracle缩小表空间

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

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

[sql]

SELECT

tablespace_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 max

FROM ( SELECT tablespace_name

, sum(bytes) AS sum_alloc

, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max

FROM dba_data_files

GROUP BY tablespace_name

)

,( SELECT tablespace_name AS fs_ts_name

, sum(bytes) AS sum_free

FROM dba_free_space

GROUP 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：

[sql]

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

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