代码改变世界

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

2015-04-16 16:58  潇湘隐者  阅读(1913)  评论(0编辑  收藏  举报

在一个数据库使用下面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.

clip_image001

当时傻眼了,难道我搞错了, 难道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掉这个表,然后修改该参数重新创建该表。

clip_image002

clip_image003

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