分区表里大量碎片，不能重用

gavintgh

最近发现用户有很多表有固定的分区，然后每天插入大量数据在各表各分区中，之后再删除一些过期的数据，理论上说保存的数据量应该是相对稳定的，因为保存的数据日期数都是10天的数据而已。但是却发现那个用户下每天新增2G的使用量，没多久就把硬盘给用满了。。
结果用了以下的sql reorg 后，发现

--before reorg

Schema allocated: 87G

--After reorg

Schema allocated:32G

DBA action:

alter table table_name move partition partition_name;
alter table table_name move partition partition_name;
alter table table_name move partition partition_name;
alter table table_name move partition partition_name;
...
...
...

我的问题是，为什么之前删除的空间没有被重用，而在不停的去新增空间

kisswolf

1. 不能重用，HWM的问题吧

2. SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME)  FROM USER_TABLES WHERE   
   TABLE_NAME='TABLE_NAME';
   
      看下INITIAL是多少。
  
   MOVE操作会根据原表的INITIAL大小为新表建立第1个EXTENT。MOVE不会自动减少表的初始扩展的大小。



不对之处，请大家指正。

parknkjun

MOVE操作是不会释放空间的。

yobyin

楼上的不对，move 是会释放空间的。
请lz 检查有无用append 这样的hint 提示

gavintgh

非常感谢各位的回复，MOVE是可以释放空间的，我从87G到32G就是执行了MOVE的动作

gavintgh

可能和HWM有关，但是我理解不了。。。再看看，到底在delete 和 insert 之间发生了什么，理论上不会每天增加2G的，毕竟保存的日期数是一样的。。。

不了峰

insert语句是如何写的?

gavintgh

没去看，如何写的，应该就一般的INSERT，没什么特别

yobyin

分析一下有无chained rows 有可能行迁移引的起的

Stone

我的理解是如果单纯的使用delete去处理旧数据，而insert去继续增加数据，那么新insert的数据会在高水位以后，所以表会继续扩展，空间会继续占用。但是既然设计用了分区，如果能根据日期进行分区的drop的话，应该是可以及时回收空间的。

可以参考下面的文章解释，应该是比较清楚啦 :)
http://www.databasejournal.com/features/oracle/article.php/3070581/Deleting-Historical-Data-on-Oracle-Databases.htm

Highwater Mark and Delete Operation

Highwater Mark (HWM)

For each object, Oracle maintains a record of the highest relative block used to hold data.

HWM is the last data block formatted to receive data, not necessarily carrying data. The table highwater mark (HWM) is the number stored in the table segment header. Adding new data to the table moves HWM to the next block, or to the last used block. Oracle optimizer relies on the HWM during calculation for the full table scan, and on full table scans, Oracle optimizer will always scan all database blocks
until the HWM pointer. This highwater mark is maintained in multiples of five blocks. Oracle has provided a way to find HWM for the table using the DBMS_SPACE package, which is not dependent on the analyzed status of the table. Deleting data from the table leaves the HWM unchanged. The next table insert will use the first free block on the free block list, which is beyond the highwater mark causing the table to continue growing.

gavintgh

Thanks a lot Stone,  I already suggest app team to use parition - subpartition to maintain housekeeping by drop subpartition instead of delete, whilest currently they have only partition table with fix country code as partition key and insert/delete data everyday, unfortunately it is already live..so it is very good lessen in their future design/data modeling.

For this case, as dba, i have to add DBA maintenance job to reorg table bi-weekly.

gavintgh

I think this post could be close unless anyone else have different comment..Thanks a lot for everyone's help/involved. You input is really appreciated.

Stone

gavintgh 发表于 2013-3-24 21:21
Thanks a lot Stone,  I already suggest app team to use parition - subpartition to maintain housekeep ...

Happy to do any favor and good to know the situation  :)