命令：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博客 ” ，链接：https://blog.itpub.net/29014732/viewspace-772729/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
降低HWM的方法之move
下一篇：
rowid详解
请登录后发表评论
登录
全部评论
