alter table move和alter table shrink space的区别

Alter table move和alter table shrinkspace的区别

1）使用alter tablemove，会把表格最多收缩到创建表格时的storage子句指定的初始大小，使用alter table shrink space，则不受此限制。
2）使用alter table move之后，索引会无效，需要重建，使用alter table shrink space，则不会使索引无效。
3）只能在表格所在的表空间是自动段空间管理（创建tablespace时指定了SEGMENT SPACE MANAGEMENT AUTO子句）的时候，才能使用altertable shrink space。
4）可以使用alter table shrink space compact来对表格进行碎片整理，而不调整HWM，之后再次调用altertable shrink space来释放空间。
5）可以使用alter table shrink space cascade来同时对索引都进行收缩，这等同于同时执行alterindex shrink space。

参考：http://www.2cto.com/database/201201/117816.html

            http://blog.youkuaiyun.com/wyzxg/article/details/5631721

案例(区别1)：

--1.建立测试表，设置表的初始大小为5m
SQL> create table t storage(initial 5m) as select * from all_objects;


Table created.
--进行表分析 并查看表的初始空间使用情况
SQL> create table t storage(initial 5m) as select * from all_objects;


Table created.
--a.从user_tables查询，获取分配给表的物理空间数量，而不管空间是否被使用


SQL> select segment_name,extents,blocks,initial_extent from user_segments where segment_name='T';


SEGMENT_NAME
--------------------------------------------------------------------------------
   EXTENTS     BLOCKS INITIAL_EXTENT
---------- ---------- --------------
T
         9       1152        5242880
--b.从user_segments查询，查询表的实际使用空间
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T                                   71954       1045            0
--c.通过dbms_rowid包获取的block数
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t;


COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                               1025
--通过show_space获取的空间使用情况
SQL> exec show_space('T');
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        .....................           1,025
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             107
Unused Bytes............................         876,544
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           3,072
Last Used Block.........................              21


PL/SQL procedure successfully completed.
--delete操作之后重新查看空间使用情况
SQL> delete from t where rownum<50000;


49999 rows deleted.


SQL> exec dbms_stats.gather_table_stats(user,'T');


PL/SQL procedure successfully completed.


SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T                                   21955       1045            0


SQL> select segment_name,extents,blocks,initial_extent from user_segments where segment_name='T';


SEGMENT_NAME
--------------------------------------------------------------------------------
   EXTENTS     BLOCKS INITIAL_EXTENT
---------- ---------- --------------
T
         9       1152        5242880


SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from t;


COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
                                                  315


SQL> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................             711
Full Blocks        .....................             314
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             107
Unused Bytes............................         876,544
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           3,072
Last Used Block.........................              21


PL/SQL procedure successfully completed.
--alter table move后的空间使用情况
SQL> alter table t move;


Table altered.


SQL> exec dbms_stats.gather_table_stats(user,'T');


PL/SQL procedure successfully completed.


SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T                                   21955        322            0
SQL> select segment_name,blocks,initial_extent from user_segments where segment_name='T';


SEGMENT_NAME
--------------------------------------------------------------------------------
    BLOCKS INITIAL_EXTENT
---------- --------------
T
       640        5242880


SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from T;


COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
                                                  314
SQL> exec show_space('T');
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        .....................             314
Total Blocks............................             640
Total Bytes.............................       5,242,880
Total MBytes............................               5
Unused Blocks...........................             318
Unused Bytes............................       2,605,056
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,152
Last Used Block.........................              66


PL/SQL procedure successfully completed.
--alter table shrink space后的空间使用情况
SQL> alter table t enable row movement;


Table altered.


SQL> alter table t shrink space;


Table altered.


SQL> exec dbms_stats.gather_table_stats(user,'T');


PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T                                   21955        322            0
SQL> select segment_name,blocks,extents,initial_extent from user_segments where segment_name='T';


SEGMENT_NAME
--------------------------------------------------------------------------------
    BLOCKS    EXTENTS INITIAL_EXTENT
---------- ---------- --------------
T
       328          3        5242880




SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from t;


COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
                                                  314


SQL> exec show_space('T');
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        .....................             314
Total Blocks............................             328
Total Bytes.............................       2,686,976
Total MBytes............................               2
Unused Blocks...........................               6
Unused Bytes............................          49,152
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,152
Last Used Block.........................              66


PL/SQL procedure successfully completed.