Oracle 数据库中的空间回收和表收缩

最近遇到了oracle数据库中的一个问题，数据库文件异常偏大，但实际业务中数据量并不大，经过查询具体的表数据空间占用情况，发现即使是一些空表，占用空间也很大。查了下资料，实际是数据表由于之前存在过很多数据，造成表的的水位线 (HWM)很高，即使数据删除后也无法后退来减小表大小和回收空间。采用以下方式得到解决。

表空间使用情况查询

可使用下面语句查询表空间的大小及使用情况：

SELECT TABLESPACE_NAME "表空间名称",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || ''           "总空间",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "未使用",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "已使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "已使用比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'SYS%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

数据表空间使用情况查询

如果表空间过大，但实际项目中数据量并不大，需要进一步查询哪些表占用了较大空间。可以使用以下语句查询每张表分配的空间大小：

select * from dba_segments where owner='user' order by round(BYTES/1024/1024,2) desc

使用shrink语句收缩数据表

查询出空间占用较大的表之后，可以使用shrink语句进行表数据的收缩：

--启用行移动
alter table tablename enable row movement;
--整理碎片回收空间 并连同表的级联对象一起整理(比如索引)
alter table tablename shrink space cascade;
--禁用行移动
alter table tablename disable row movement;

关于shrink语句的使用
优点：

1. 使用参数cascade，可同时收缩表上的索引
2. shrink语句执行后不会导致索引失效
3. oracle 10g以上版本支持shrink语句
   限制：
4. shrink 操作需满足表空间是本地管理和自动段空间管理（10g、11g默认就是这样）
5. IOT索引组织表不能用shrink
6. 用rowid创建的物化视图的基表不能用shrink
7. 带有函数索引的表不能用shrink
8. SECUREFILE 类型的大对象不能用shrink
9. 压缩表不能用shrink

使用move语句收缩数据表

使用move语句同样可以收缩数据表，但是收缩完成后会导致所以失效，必须重建索引。

--启用行移动
alter table tablename enable row movement;
--执行后会导致索引失效，不推荐使用
alter table tablename move;
--禁用行移动
alter table tablename disable row movement;
--查询失效索引，执行以下语句的结果集
select 'alter index '||index_name||' rebuild online;' from  user_indexes where status <> 'VALID' and index_name not like'%$$';

总结

新建表空间时，可以指定表空间的大小及自增长空间大小，以此来避免数据库占用空间过大的问题。
关于数据库表空间碎片整理及表数据收缩的逻辑原理，可以参考链接中文章。
链接: Oracle 数据库中的碎片管理和表收缩.