Oracle move和shrink釋放高水位空間 （一）

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 pt_table modify  PARTITION P1 shrink space cascade;  分區表

shrink的優點

1.可在線執行

2.可使用參數cascade，同時收縮表上的索引

3.執行后不會導致索引失效

4.可避免alter table move執行過程中占用很多表空間（如果表10G大小，那alter table move差不多還得需要10G空間才能執行）。

二、move

1、move table的功能：

 ①：將一個table從當前的tablespace上移動到另一個tablespace上：

 ②：來改變table已有的block的存儲參數,如：alter table t move storage (initial 30k next 50k);

 ③：move操作也可以用來解決table中的行遷移的問題。

2、使用move的一些注意事項：

 ①：table上的index需要rebuild：

   在前面我們討論過，move操作后，數據的rowid發生了改變，我們知道，index是通過rowid來fetch數據行的，所以，table上的index是必須要rebuild的。

    alter index index_name rebuild online；

 ②：move時對table的鎖定

   當我們對table進行move操作時，查詢v$locked_objects視圖可以發現，table上加了exclusive lock

 ③：關于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會同時壓縮索引，

四、實戰實驗：

 實驗環境：Oracle11.2.0.4

[oracle@dbs ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 14:44:59 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

1、創建兩張測試表：test_1 和 test_2

SQL> create table test_1 (name varchar2(10)) storage (initial 500m next 1m);
Table created.
SQL> create table test_2 (name varchar2(10)) storage (initial 500m next 1m);
SQL>  create index idx_test1 on test_1(name);
Index created.
SQL>  create index idx_test2 on test_2(name);
Index created.

2、插入數據，并收集統計信息：

SQL> insert into test_1 values('zhang');
SQL> insert into test_1 values('zhang');
SQL> insert into test_2 values('zhang');
SQL> insert into test_2 values('zhang');
SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_1',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_2',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>

3、查看兩張表的blocks信息：

SQL> select B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 
    USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME; 
SEGMENT_NAME                                                                          BLOCKS B.BLOCKS*8096/1024/1024     BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_1                                                                                 64512               498.09375        222              1.71405029            0
TEST_2                                                                                 64512               498.09375        222              1.71405029            0
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST_1','TEST_2');
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST_1                                222            0
TEST_2                                222            0
SQL> select owner,segment_name,sum(bytes)/1024/1024 MB from dba_segments where tablespace_name='TEST' and segment_type like '%TAB%' group by owner,segment_name order by MB desc;
OWNER                          SEGMENT_NAME                                                                              MB
------------------------------ --------------------------------------------------------------------------------- ----------
ADMIN                          TEST_2                                                                                   504
ADMIN                          TEST_1                                                                                   504
SQL> select index_name,table_name,status from user_indexes where table_name in ('TEST_1','TEST_2');    ---索引狀態都正常
INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
IDX_TEST2                      TEST_2                         VALID
IDX_TEST1                      TEST_1                         VALID
SQL>

 ----從上面可以看出，由于我們預分配給了兩張表500M，那么他們倆現在一共有64512個blocks，共有500M，而實際只占用了222個，

4、刪除兩張表的數據，并收集統計信息然后查看兩張表的blocks信息：

SQL> delete from test_1 where rownum <=1;
1 row deleted.
SQL> delete from test_2 where rownum <=1;
1 row deleted.
SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_1',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>  exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_2',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST_1','TEST_2');
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST_1                                222            0
TEST_2                                222            0
SQL> select B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 
  2      USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME; 
SEGMENT_NAME                                                                          BLOCKS B.BLOCKS*8096/1024/1024     BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_1                                                                                 64512               498.09375        222              1.71405029            0
TEST_2                                                                                 64512               498.09375        222              1.71405029            0
SQL> 
SQL> select index_name,table_name,status from user_indexes where table_name in ('TEST_1','TEST_2');    ---此時索引狀態都正常
INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
IDX_TEST2                      TEST_2                         VALID
IDX_TEST1                      TEST_1                         VALID

---從上面可以看出，雖然刪除了表的數據，但是空間并沒有釋放，沒有釋放的空間包括高水位線以上和高水位線以下。（高水位線上面的空間就是預分配的空間 減去 實際占用的空間；

高水位線以下的空間就是數據實際占用的空間--因為delete是不會是否空間的，也就是說高水位一直存在除非新插入的數據將其覆蓋）

5、對test_1表進行move操作：

SQL> alter table test_1 move;
Table altered.
SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_1',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 
  2         USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME; 
SEGMENT_NAME                                                                          BLOCKS B.BLOCKS*8096/1024/1024     BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_2                                                                                 64512               498.09375        222              1.71405029            0
TEST_1                                                                                 64384              497.105469         35              .270233154            0
SQL> select index_name,table_name,status from user_indexes where table_name in ('TEST_1','TEST_2'); 
INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
IDX_TEST2                      TEST_2                         VALID
IDX_TEST1                      TEST_1                         UNUSABLE

---從上面可以看出，對表做了move后，該表實際占用的空間已經釋放了，但是預分配的空間始終沒有變化，這說明move操作會釋放高水位以下的空間，但是不會釋放高水位以上的空間；同時 test_1表的索引已經失效了！

6、對test_2表做shrink space操作：

SQL> alter table test_2 enable row movement;
Table altered.
SQL> alter table test_2 shrink space;
Table altered.
SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_2',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 
  2            USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME; 
SEGMENT_NAME                                                                          BLOCKS B.BLOCKS*8096/1024/1024     BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_2                                                                                    40              .308837891          1              .007720947            0
TEST_1                                                                                 64384              497.105469         35              .270233154            0
SQL> 
SQL> select index_name,table_name,status from user_indexes where table_name='TEST_2';
INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
IDX_TEST2                      TEST_2                         VALID
SQL>

---從上面可以看出預分配的空間全部釋放了，說明shrink space 同樣會移動高水位，但也會釋放申請的空間，是在高水位上下(below and above HWM)都有的操作，并且索引不會失效。

注意：

①：使用move時，會改變一些記錄的ROWID，所以MOVE之后索引會變為無效，需要REBUILD。

②：使用shrink space時，索引會自動維護。如果在業務繁忙時做壓縮，可以先shrink space compact，來壓縮數據而不移動HWM，等到不繁忙的時候再shrink space來移動HWM。

③：索引也是可以壓縮的，壓縮表時指定Shrink space cascade會同時壓縮索引，也可以alter index xxx shrink space來壓縮索引。

④：shrink space需要在表空間是自動段空間管理的，所以system表空間上的表無法shrink space。

---補充，move 也可以做到真正的壓縮分配空間，只要指定STORAGE參數即可。：

SQL> alter table test_1 move storage (initial 1m);