alter table move 和 alter table shrink space的區別
move 和shrink 的共同點
1、收縮段
2、消除部分行遷移
3、消除空間碎片
4、使資料更緊密
語法：
alter table TABLE_NAME shrink space [compact|cascate]
segment shrink執行的兩個階段：
1、資料重組(compact):
舉例
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：
move解決的問題
1、將一個table從當前的tablespace上移動到另一個tablespace上：
使用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)都有的操作。
SQL> spool off;
1、收縮段
2、消除部分行遷移
3、消除空間碎片
4、使資料更緊密
shrink
語法：
alter table TABLE_NAME shrink space [compact|cascate]
segment shrink執行的兩個階段：
1、資料重組(compact):
透過一系列insert、delete操作，將資料儘量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。2、HWM調整：第二階段是調整HWM位置，釋放空閒資料塊。
由於涉及到rowid的改變，需要enable row movement.同時要disable基於rowid的trigger.這一過程對業務影響比較小。
此過程需要在表上加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;2、來改變table已有的block的儲存引數,如：
alter table TABLE_NAME move ; --在原來的表空間內部移動。
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)都有的操作。
SQL> spool off;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-1162174/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ALTER TABLE MOVE和SHRINK SPACE區別
- ALTER TABLE MOVE | SHRINK SPACE區別
- alter table move 與shrink space的區別
- alter table move跟shrink space的區別
- alter table move跟shrink space的區別（轉）
- Oracle 11g alter table move與shrink spaceOracle
- oracle 10g__alter table shrink space compactOracle 10g
- table move 與 shrink 的區別
- [Oracle] Shrink space & Table move比較Oracle
- alter table table_name move ; 在自身表空間move是如何操作的？
- v$lock之alter table drop column與alter table set unused column區別系列五
- Alter table for ORACLEOracle
- alter table列管理的一些區別
- 表、索引遷移表空間alter table move索引
- mysql的ALTER TABLE命令MySql
- alter database和alter system和alter session的區別DatabaseSession
- oracle10g_alter table shrink space_compact_cascade回收空間測試(一)Oracle
- oracle alter table詳解Oracle
- alter table using indexIndex
- How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINKOracle 10g
- 測試alter table storage及dbms_space_admin包
- 測試alter table shrink space compact cascade及學習user_tables相關列的含義
- alter table語法增補(一）
- 忍不住問下alter system 和alter database的區別Database
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- drop table和truncate table的區別
- alter system events與alter system event的區別
- oracle 中 ALTER TABLE ADD default 的明確Oracle
- alter system switch logfile和alter system archive log current的區別Hive
- alter system switch logfile 和 alter system archive log current 的區別Hive
- alter table engine=memory ERROR 1114Error
- ALTER DATABASE 與 ALTER TABLESPACE OFFLINE的區別Database
- [轉]alter system switch logfile和alter system archive log current的區別Hive
- [重慶思莊每日技術分享]-在為表新增了列後執行ALTER TABLE SHRINK SPACE 提示ORA-8102
- alter table nologging /*+APPEND PARALLEL(n)*/APPParallel
- ALTER TABLE causes auto_increment resulting key 'PRIMARY'REM
- oracle10g_alter table_測試3Oracle
- oracle shrink tableOracle