Oracle Table Fragmentation

最新推荐文章于 2021-04-03 19:41:58 发布

原创最新推荐文章于 2021-04-03 19:41:58 发布 · 176 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #HTML

本文探讨了数据库中表碎片化的问题，特别是在频繁进行插入、更新和删除操作的情况下。通过一个实例说明了表碎片化如何导致实际占用空间远大于数据大小，并提出使用分区表可以避免此类问题。

If only insert data into the table, [color=red]table fragmentation[/color] will never happen. But if insert, update and delete are executed on a table frequently, table fragmentation will come out. If a table takes more disk space than its size, it is called table fragmentation. We could know this by querying USER_SEGMENTS and USER_TABLES.

Yesterday, customers complained that the disk is full. We found that one tablespace becomes very large, about 30G. Actually, no more than 10G data is contained in this tablespace. And I found one table uses approximate 20G, actually this table only has
2G data. Then I realize it is table fragmentation. We could avoid this kind of problem if using partition table.

How to handle table fragmentation? Refer to the links below.

[url]http://www.orafaq.com/node/1936[/url]
[url]http://oracle-online-help.blogspot.com/2007/02/identify-and-fix-table-fragmentation-in.html[/url]