最近遇到了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语句的使用
优点:
- 使用参数cascade,可同时收缩表上的索引
- shrink语句执行后不会导致索引失效
- oracle 10g以上版本支持shrink语句
限制: - shrink 操作需满足表空间是本地管理和自动段空间管理(10g、11g默认就是这样)
- IOT索引组织表不能用shrink
- 用rowid创建的物化视图的基表不能用shrink
- 带有函数索引的表不能用shrink
- SECUREFILE 类型的大对象不能用shrink
- 压缩表不能用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 数据库中的碎片管理和表收缩.