通过space_usage看truncate table xx reuse storage的hwm下降及extent释放

最新推荐文章于 2025-11-06 18:03:36 发布

转载最新推荐文章于 2025-11-06 18:03:36 发布 · 94 阅读

本文通过实验展示了在Oracle数据库中使用TRUNCATE TABLE ... REUSE STORAGE与TRUNCATE TABLE ... DROP STORAGE的区别。前者仅更新数据字典并降低高水位线(HWM)，而后者会在最后释放表所占的范围(extent)。

通过以下实验，发现truncate table xx reuse storage 是先更新数据字典，然后降低HWM , 到最后 truncate table xx drop storage ; 时才对这个table占用的extent 进行释放。且明显感觉最后释放空间时间稍长一些。

creata table aa as select * from dba_objects ;
insert into aa select * from dba_objects ;
insert into aa select * from dba_objects ;
insert into aa select * from dba_objects ;
insert into aa select * from dba_objects ;
insert into aa select * from dba_objects ;

SQL> select BYTES, EXTENTS from dba_segments where segment_name ='AA' ;

BYTES EXTENTS
---------- ----------
37748736 51

SQL> set serveroutput on;

SQL>
declare
    l_fs1_bytes number;
    l_fs2_bytes number;
    l_fs3_bytes number;
    l_fs4_bytes number;
    l_fs1_blocks number;
    l_fs2_blocks number;
    l_fs3_blocks number;
    l_fs4_blocks number;
    l_full_bytes number;
    l_full_blocks number;
    l_unformatted_bytes number;
    l_unformatted_blocks number;
begin
      dbms_space.space_usage('DFMS',
         'AA',
         'TABLE',
         fs1_bytes=> l_fs1_bytes,
         fs1_blocks         => l_fs1_blocks,
         fs2_bytes          => l_fs2_bytes,
         fs2_blocks         => l_fs2_blocks,
         fs3_bytes          => l_fs3_bytes,
         fs3_blocks         => l_fs3_blocks,
         fs4_bytes          => l_fs4_bytes,
         fs4_blocks         => l_fs4_blocks,
         full_bytes         => l_full_bytes,
         full_blocks        => l_full_blocks,
         unformatted_blocks => l_unformatted_blocks,
         unformatted_bytes => l_unformatted_bytes   );
      dbms_output.put_line(' FS1 Blocks (0~25%) = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
      dbms_output.put_line(' FS2 Blocks(25~50%) = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
      dbms_output.put_line(' FS3 Blocks(50~75%) = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
      dbms_output.put_line(' FS4 Blocks (75~100%)= '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
      dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
dbms_output.put_line(' Unformatted Blocks = '||l_unformatted_blocks||' and Bytes = '||l_unformatted_bytes);
   end;
   /

FS1 Blocks (0~25%) = 0 and Bytes = 0
FS2 Blocks(25~50%) = 0 and Bytes = 0
FS3 Blocks(50~75%) = 1 and Bytes = 8192
FS4 Blocks (75~100%)= 14 and Bytes = 114688
Full Blocks = 3709 and Bytes = 30384128
Unformatted Blocks = 48 and Bytes = 393216

PL/SQL procedure successfully completed.

SQL> truncate table dfms.aa reuse storage ;

Table truncated.

SQL> select BYTES, EXTENTS from dba_segments where segment_name ='AA' ;

BYTES EXTENTS
---------- ----------
37748736 51

SQL> 再次执行上面的space_usage .

FS1 Blocks (0~25%) = 0 and Bytes = 0
FS2 Blocks(25~50%) = 0 and Bytes = 0
FS3 Blocks(50~75%) = 0 and Bytes = 0
FS4 Blocks (75~100%)= 0 and Bytes = 0
Full Blocks = 0 and Bytes = 0
Unformatted Blocks = 0 and Bytes = 0

SQL> set serveroutput off ;

SQL> truncate table dfms.aa drop storage ;

SQL> select BYTES, EXTENTS from dba_segments where segment_name ='AA' ;

BYTES EXTENTS
---------- ----------
65536 1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-681564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-681564/