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

四、實戰實驗：

實驗環境： 11.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);

Oracle move和shrink釋放高水位空間

相關文章