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

159次閱讀

共計 6236 個字符，預計需要花費 16 分鐘才能閱讀完成。

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) 都有的操作。