数据库用久了难免会出现没有回收的空间,如果空间太大可使用以下方法进行回收。
查询用个表所占用的空间:
- 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;