INITIAL参数设置导致TRUNCATE TABLE不能降低高水位线案例

在一个数据库使用下面SQL找出了一批需要降低高水位线的表，其中有几个表没有数据，于是我打算用TRUNCATE来降低高水位线HWM

SELECT a.owner,
       a.segment_name,
       a.segment_type,
       a.tablespace_name,
       a.blocks              "real block",
       a.bytes / 1024 / 1024 "realSizeMB",
       b.last_analyzed,
       b.num_rows
FROM   dba_segments a,
       dba_tables b
WHERE  a.owner = b.owner
       AND a.segment_name = b.table_name
       AND B.partitioned = 'NO'
       AND b.num_rows < 5000
       AND a.blocks > 1000
       AND a.bytes / 1024 / 1024 > 500
ORDER  BY 6 DESC

我们先看看其中一个表的空间使用情况，如下所示，结果我对该表执行了TRUNCATE后，发现高水位线HWM根本没有变化

SQL> exec show_space('INV_MONTH_END_LOCATION', 'INVENTORY');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................         434,176
Total Bytes.............................   3,556,769,792
Total MBytes............................           3,392
Unused Blocks...........................         434,142
Unused Bytes............................   3,556,491,264
Last Used Ext FileId....................              40
Last Used Ext BlockId...................               9
Last Used Block.........................              34

PL/SQL procedure successfully completed.

SQL> exec show_space('INV_MONTH_END_LOCATION', 'INVENTORY');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................         434,176
Total Bytes.............................   3,556,769,792
Total MBytes............................           3,392
Unused Blocks...........................         434,142
Unused Bytes............................   3,556,491,264
Last Used Ext FileId....................              40
Last Used Ext BlockId...................               9
Last Used Block.........................              34

PL/SQL procedure successfully completed.

当时傻眼了，难道我搞错了， 难道TRUNCATE不会释放存储空间，降低高水位线？于是查了一下资料，确认TRUNCATE会释放存储空间，降低高水位线。那么问题出在哪里呢？于是我对该表重新收集了一下统计信息后发现依然如此

SQL> exec dbms_stats.gather_table_stats('INVENTORY','INV_MONTH_END_LOCATION', cascade=>true);

PL/SQL procedure successfully completed.

最后我生成了创建该表的SQL语句，终于发现了问题。如下截图所示。initial与next决定创建segment及扩展segment，initial表示初始化时分配给该表的段大小为3,556,769,792Byte。也就是3392MB。但是已经不知道当时谁建表示设定了这个参数，于是只能DROP掉这个表，然后修改该参数重新创建该表。

另外，如果是这个情况下，使用ALTER MOVE也是不能释放表空间，降低高水位线的。切记切记。