测试表空间压缩是否可行
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用户表了
