oracle缩小表空间

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

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

 

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：

 

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

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