ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 降低HWM的方法之shrink

降低HWM的方法之shrink

原创 Linux操作系统 作者:Power08 时间:2013-09-15 14:32:44 0 删除 编辑
命令:alter table shrink space
前提:允许行移动,alter table enable row movement且不再system表空间

测试:

SQL> create table test tablespace users as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('sys','test');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables  WHERE table_name='TEST';

TABLE_NAME
------------------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST
     17345  242 0


SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';

BYTES/1024/1024
---------------
     2

SQL> delete test where rownum<14000;

13999 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('sys','test');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables  WHERE table_name='TEST';

TABLE_NAME
------------------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST
      3346  242 0


SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';

BYTES/1024/1024
---------------
     2

仅仅行数下降,其余信息都保持不变



SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space;

Table altered.

SQL> exec dbms_stats.gather_table_stats('sys','test');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables  WHERE table_name='TEST';

TABLE_NAME
------------------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST
      3346   48 0 -----------------行数,块数下降


SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';

BYTES/1024/1024
---------------
 .4375 ----------------------------表所占空间也下降

所有信息都下降








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

下一篇: rowid详解
请登录后发表评论 登录
全部评论

注册时间:2013-07-01

  • 博文量
    17
  • 访问量
    44496