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