alter table move 和 alter table shrink space的區別是什么

alter table move 和 alter table shrink space的區別是什么，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

move 和shrink 的共同點
1、收縮段
2、消除部分行遷移
3、消除空間碎片
4、使數據更緊密

shrink

語法：
  alter table TABLE_NAME shrink space [compact|cascate]

segment shrink執行的兩個階段：
1、數據重組(compact):

通過一系列insert、delete操作，將數據盡量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。
由于涉及到rowid的改變，需要enable row movement.同時要disable基于rowid的trigger.這一過程對業務影響比較小。

2、HWM調整：第二階段是調整HWM位置，釋放空閑數據塊。

此過程需要在表上加X鎖，會造成表上的所有DML語句阻塞。在業務特別繁忙的系統上可能造成比較大的影響。
注意：shrink space語句兩個階段都執行。
shrink space compact只執行第一個階段。
如果系統業務比較繁忙，
可以先執行shrink space compact重組數據,然后在業務不忙的時候再執行shrink space降低HWM釋放空閑數據塊。

舉例

  alter table TABLE_NAME shrink space compact;  只整理碎片 不回收空間， 
  alter table TABLE_NAME shrink space;                 整理碎片并回收空間。

  alter table TABLE_NAME shrink space cascade;    整理碎片回收空間 并連同表的級聯對象一起整理(比如索引)
  --分區表
  alter table ticket modify  PARTITION P28071 shrink space cascade

shrink的優點
1.可在線執行
2.可使用參數cascade，同時收縮表上的索引
3.執行后不會導致索引失效
4.可避免alter table move執行過程中占用很多表空間（如果表10G大小，那alter table move差不多還得需要10G空間才能執行）。

shrink使用條件:
使用步驟
  1. alter table t1 enable  ROW MOVEMENT;
  2. shrink 操作
  3. alter table t1 disable  ROW MOVEMENT;

shrink使用限制：
Shrink 操作需滿足表空間是本地管理和自動段空間管理（10g、11g默認就是這樣），
以下情況不能用shrink：

IOT索引組織表
用rowid創建的物化視圖的基表
帶有函數索引的表
SECUREFILE 類型的大對象
壓縮表

move

move解決的問題
1、將一個table從當前的tablespace上移動到另一個tablespace上：

alter table t move tablespace tablespace_name;
alter table TABLE_NAME move ;                                                    --在原來的表空間內部移動。

2、來改變table已有的block的存儲參數,如：

    alter table t move storage (initial 30k next 50k);

3、另外，move操作也可以用來解決table中的行遷移的問題。

使用move的一些注意事項：
1、table上的index需要rebuild：
    在前面我們討論過，move操作后，數據的rowid發生了改變，我們知道，index是通過rowid來fetch數據行的，所以，table上的index是必須要rebuild的。
    alter index index_name rebuild online；
2、move時對table的鎖定
    當我們對table進行move操作時，查詢v$locked_objects視圖可以發現，table上加了exclusive lock
3、關于move時空間使用的問題：
    當我們使用alter table move來降低table的HWM時，有一點是需要注意的，這時，當前的tablespace中需要有1倍于table的空閑空間以供使用。


move和hrink的區別是：
1、move后，表在表空間中的位置肯定會變，可能前移也可能后移，一般來說如果該表前面的表空間中有足夠空間容納該表，則前移，否則后移。
2、hrink后，表在表空間中的位置肯定不變，也就是表的段頭位置不會發生變化。

3、Move會移動高水位，但不會釋放申請的空間，是在高水位以下(below HWM)的操作。
4、shrink space 同樣會移動高水位，但也會釋放申請的空間，是在高水位上下(below and above HWM)都有的操作。

5、使用move時，會改變一些記錄的ROWID，所以MOVE之后索引會變為無效，需要REBUILD。
6、使用shrink space時，索引會自動維護。如果在業務繁忙時做壓縮，
    可以先shrink space compact，來壓縮數據而不移動HWM，等到不繁忙的時候再shrink space來移動HWM。


7、shrink可以單獨壓縮索引，alter index xxx shrink space來壓縮索引。另外、壓縮表時指定Shrink space cascade會同時壓縮索引，




測試

SQL>
SQL> drop table  test  purge;
SQL> drop table  test2        purge;
SQL>
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
SQL> create table test2 (id number) storage (initial 10m next 1m) tablespace users;
SQL>
SQL> insert into test values(1);
SQL> insert into test2 values(1);
SQL>
SQL> analyze table test compute statistics;
SQL> analyze table test2 compute statistics;
SQL>
SQL> col SEGMENT_NAME for a10;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2');
SEGMENT_NA                                EXTENTS                                 BLOCKS                                   INIT
---------- -------------------------------------- -------------------------------------- --------------------------------------
TEST2                                           3                                   1280                                     10
TEST                                            3                                   1280                                     10
--兩個表，原始申請的分區數和數據塊數
SQL> col TABLE_NAME for a10;
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2');
TABLE_NAME                                 BLOCKS                           EMPTY_BLOCKS
---------- -------------------------------------- --------------------------------------
TEST                                           46                                   1234
TEST2                                          46                                   1234
--兩個表，實際使用的數據塊數46，空閑數據塊數1234。
SQL>
SQL> begin
  2   for i in 1..100000 loop
  3  insert into test values(i);
  4  insert into test2 values(i);
  5  end loop;
  6  end;
  7  /
SQL>
SQL> analyze table test compute statistics;
SQL> analyze table test2 compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2');
SEGMENT_NA                                EXTENTS                                 BLOCKS                                   INIT
---------- -------------------------------------- -------------------------------------- --------------------------------------
TEST2                                           3                                   1280                                     10
TEST                                            3                                   1280                                     10
--插入大量數據后，兩個表的原始申請分區數和數據塊數，沒有變化
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2');
TABLE_NAME                                 BLOCKS                           EMPTY_BLOCKS
---------- -------------------------------------- --------------------------------------
TEST                                          174                                   1106
TEST2                                         174                                   1106
--插入大量數據后，兩個表實際使用的數據塊數發生了變化，使用174塊，空閑1106塊。174就是高水位線
SQL>
SQL>
SQL> delete from test where rownum<=50000;
SQL> delete from test2 where rownum<=50000;
SQL>
SQL> analyze table test compute statistics;
SQL> analyze table test2 compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2');
SEGMENT_NA                                EXTENTS                                 BLOCKS                                   INIT
---------- -------------------------------------- -------------------------------------- --------------------------------------
TEST2                                           3                                   1280                                     10
TEST                                            3                                   1280                                     10
--刪除大量數據后，兩個表的原始申請分區數和數據塊數，沒有變化


SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2');
TABLE_NAME                                 BLOCKS                           EMPTY_BLOCKS
---------- -------------------------------------- --------------------------------------
TEST                                          174                                   1106
TEST2                                         174                                   1106
--刪除大量數據后，兩個表實際使用的數據塊數也沒有發生變化。即delete不會釋放空間


SQL>
SQL>
SQL> alter table test move;
SQL>
SQL> analyze table test compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2');
SEGMENT_NA                                EXTENTS                                 BLOCKS                                   INIT
---------- -------------------------------------- -------------------------------------- --------------------------------------
TEST2                                           3                                   1280                                     10
TEST                                            3                                   1280                                     10
--對test表，做move操作，原始申請分區和數據塊數，沒有變化。


SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2');
TABLE_NAME                                 BLOCKS                           EMPTY_BLOCKS
---------- -------------------------------------- --------------------------------------
TEST                                           95                                   1185
TEST2                                         174                                   1106
--對test表，做move操作，實際使用數據塊數發生變化。
Move會移動高水位，但不會釋放申請的空間，是在高水位以下(below HWM)的操作。


SQL>
SQL>
SQL> alter table test2 enable row movement;
SQL> alter table test2 shrink space;
SQL> analyze table test2 compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2');
SEGMENT_NA                                EXTENTS                                 BLOCKS                                   INIT
---------- -------------------------------------- -------------------------------------- --------------------------------------
TEST2                                           1                                    104                                     10
TEST                                            3                                   1280                                     10
--對test2表，做shrink操作，原始申請分區和數據塊數，發生了變化
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2');
TABLE_NAME                                 BLOCKS                           EMPTY_BLOCKS
---------- -------------------------------------- --------------------------------------
TEST                                           95                                   1185
TEST2                                          79                                     25
--對test2表，做shrink操作，實際使用數據塊數，發生了變化
shrink space 同樣會移動高水位，但也會釋放申請的空間，是在高水位上下(below and above HWM)都有的操作。

看完上述內容，你們掌握alter table move 和 alter table shrink space的區別是什么的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！