测试表空间压缩是否可行

select t.*,t.TABLESPACE_NAME,(t.BYTES)/1024/1024/1024 from dba_data_files t;

之前表空间为2g;

查看表空间使用情况

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;

表空间已用5m,实际使用3m,空闲2m,

收缩：

alter database datafile '/oracle/oradata/orcl10g/da_zddx01.dbf' resize 1024m;

测试是可行的

考虑如下问题：

压缩表空间时，如果数据运行比较频繁，那么请考虑风险。

ds数据库服务于ds应用，由于ds应用的关闭重启未执行过，考虑到尽量不改变运行情况。

B用户truncateA用户表

在a库总建立存储过程，之后将存储过程授权给b用户调度，这样b就可以truancate a用户表了

ITPub博客

oracle表空间压缩