降低HWM的方法之shrink

命令：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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29014732/viewspace-772729/