alter table move 和 alter table shrink space的區(qū)別是什么

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

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

shrink

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

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

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

2、HWM調(diào)整：第二階段是調(diào)整HWM位置，釋放空閑數(shù)據(jù)塊。

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

舉例

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

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

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

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

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

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

move

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

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

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

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

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

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


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

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

5、使用move時，會改變一些記錄的ROWID，所以MOVE之后索引會變?yōu)闊o效，需要REBUILD。
6、使用shrink space時，索引會自動維護。如果在業(yè)務(wù)繁忙時做壓縮，
    可以先shrink space compact，來壓縮數(shù)據(jù)而不移動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
--兩個表，原始申請的分區(qū)數(shù)和數(shù)據(jù)塊數(shù)
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
--兩個表，實際使用的數(shù)據(jù)塊數(shù)46，空閑數(shù)據(jù)塊數(shù)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
--插入大量數(shù)據(jù)后，兩個表的原始申請分區(qū)數(shù)和數(shù)據(jù)塊數(shù)，沒有變化
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
--插入大量數(shù)據(jù)后，兩個表實際使用的數(shù)據(jù)塊數(shù)發(fā)生了變化，使用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
--刪除大量數(shù)據(jù)后，兩個表的原始申請分區(qū)數(shù)和數(shù)據(jù)塊數(shù)，沒有變化


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
--刪除大量數(shù)據(jù)后，兩個表實際使用的數(shù)據(jù)塊數(shù)也沒有發(fā)生變化。即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操作，原始申請分區(qū)和數(shù)據(jù)塊數(shù)，沒有變化。


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操作，實際使用數(shù)據(jù)塊數(shù)發(fā)生變化。
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操作，原始申請分區(qū)和數(shù)據(jù)塊數(shù)，發(fā)生了變化
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操作，實際使用數(shù)據(jù)塊數(shù)，發(fā)生了變化
shrink space 同樣會移動高水位，但也會釋放申請的空間，是在高水位上下(below and above HWM)都有的操作。

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