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
- mysql的ALTER TABLE命令MySql
- alter table set unused column
- create table，show tables，describe table，DROP TABLE，ALTER TABLE ，怎麼使用？
- alter table nologging /*+APPEND PARALLEL(n)*/APPParallel
- alter table drop unused columns checkpoint
- ALTER TABLE修改列的不同方法
- MySQL-ALTER TABLE命令學習[20180503]MySql
- [重慶思莊每日技術分享]-在為表新增了列後執行ALTER TABLE SHRINK SPACE 提示ORA-8102
- [20191129]ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txtBloC
- mysql alter modify 和 change的區別MySql
- alter system set event和set events的區別
- MySQL alter table時執行innobackupex全備再看Seconds_Behind_MasterMySqlAST
- 透過alter table 來實現重建表，同事大呼開眼界了
- MySQL中的alter table命令的基本使用方法及提速最佳化MySql
- 線上改表工具oak-online-alter-table和pt-online-schema-change的使用限制總結
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- SQL__ALTERSQL
- MySQL ALTER命令MySql
- MySQL的create table as 與 like區別MySql
- Oracle move和shrink釋放高水位空間Oracle
- mysql加快alter操作MySql
- mysql表操作(alter)/mysql欄位型別MySql型別
- admin_move_table線上更改分割槽鍵
- table中cesllspacing與cellpadding的區別詳解padding
- alter database disable thread 2Databasethread
- ALTER SYSTEM FLUSH BUFFER_POOL
- Lua 列印table 實現型別python的repr用於table型別Python
- SQLAlchemy Table（表）類方式 – Table類和Column類SQL
- 如何獲取 alter 裡面的字串？字串
- 開心檔之MySQL ALTER命令MySql
- 2.7.6.2.1 ALTER SYSTEM SET語句中的SCOPE子句
- 【SQL】16 SQL CREATE INDEX 語句、 撤銷索引、撤銷表以及撤銷資料庫、ALTER TABLE 語句、AUTO INCREMENT 欄位SQLIndex索引資料庫REM
- alter tablespace ts_name autoextend_clause
- MySQL 資料庫 ALTER命令講解MySql資料庫
- MySQL alter 新增列對dml影響MySql
- table
- ORA-279 signalled during: alter database recover logfileDatabase
- [20190918]shrink space與ORA-08102錯誤.txt