Oracle中shrink space命令詳解
從10g開始，oracle開始提供Shrink的命令，假如我們的表空間中支援自動段空間管理(ASSM),就可以使用這個特性縮小段，即降低HWM。這裡需要強調一點，10g的這個新特性，僅對ASSM表空間有效，否則會報 ORA-10635: Invalid segment or tablespace type。
1 建立實驗環境
1.1 建立ASSM的表空間
SQL> set serveroutput on
SQL> create tablespace ASSM datafile '/oradata/ltest/assm.dbf' size 10m autoextend on SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created
SQL> select tablespace_name,
2 block_size,
3 extent_management,
4 allocation_type,
5 segment_space_management
6 from dba_tablespaces
7 where tablespace_name = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
--------------- ---------- ----------------- --------------- ------------------------
ASSM 8192 LOCAL SYSTEM AUTO
1.2 建表
SQL> create table my_objects tablespace assm as select * from all_objects;
Table created
SQL> select count(*) from my_objects;
COUNT(*)
----------
49903
2 實驗前的資訊
SQL> exec show_space('MY_OBJECTS');
Total Blocks ..........................768
Total Bytes ..........................6291456
Total MBytes ..........................6
Unused Blocks ..........................62
Unused Bytes ..........................507904
Unused KBytes ..........................496
Last Used Ext FileId....................7
Last Used Ext BlockId...................649
Last Used Block.........................66
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................0
FS3 Blocks (50-75) ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks ....................0
Full Blocks ....................686
PL/SQL procedure successfully completed
3 刪除後的資訊
然後我們隨機地從table MY_OBJECTS中刪除一部分資料：
SQL> delete from my_objects where object_name like '%C%';
17674 rows deleted
SQL> delete from my_objects where object_name like '%U%';
4687 rows deleted
SQL> delete from my_objects where object_name like '%A%';
7010 rows deleted
SQL> exec show_space('MY_OBJECTS');
Total Blocks ..........................768
Total Bytes ..........................6291456
Total MBytes ..........................6
Unused Blocks ..........................62
Unused Bytes ..........................507904
Unused KBytes ..........................496
Last Used Ext FileId....................7
Last Used Ext BlockId...................649
Last Used Block.........................66
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................212
FS3 Blocks (50-75) ....................181
FS4 Blocks (75-100) ....................245
Unformatted Blocks ....................0
Full Blocks ....................48
PL/SQL procedure successfully completed
這裡，table my_objects的HWM下有706(768 - 62)個block，其中，free space為25-50%的block有205個，free space為50-75%的block有180個，free space為75-100%的block有229個，full space的block只有45個，這種情況下，我們需要對這個table的現有資料行進行重組。
4 shink操作
要使用assm上的shink，首先我們需要使該表支援行移動，可以用這樣的命令來完成：
SQL> alter table my_objects enable row movement;
Table altered
現在，就可以來降低my_objects的HWM，回收空間了，使用命令：
SQL> alter table my_objects shrink space;
Table altered
SQL> exec show_space('MY_OBJECTS');
Total Blocks ..........................280
Total Bytes ..........................2293760
Total MBytes ..........................2.1875
Unused Blocks ..........................5
Unused Bytes ..........................40960
Unused KBytes ..........................40
Last Used Ext FileId....................7
Last Used Ext BlockId...................265
Last Used Block.........................19
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................1
FS3 Blocks (50-75) ....................1
FS4 Blocks (75-100) ....................0
Unformatted Blocks ....................0
Full Blocks ....................259
PL/SQL procedure successfully completed
在執行玩shrink命令後，此時表my_objects的HWM現在降到了276(280 - 5 + 1)的位置，而且HWM下的block的空間使用狀況，full space的block有259個，free space 為25-50% 和50-75% Block只有1個。
5 shrink space原理剖析
5.1 實驗環境
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
Table created
SQL> insert into TEST_HWM values (1, 'aa');
1 row inserted
SQL> insert into TEST_HWM values (2, 'bb');
1 row inserted
SQL> insert into TEST_HWM values (3, 'cc');
1 row inserted
SQL> insert into TEST_HWM values (4, 'ds');
1 row inserted
SQL> insert into TEST_HWM values (5, 'dss');
1 row inserted
SQL> insert into TEST_HWM values (6, 'dss');
1 row inserted
SQL> insert into TEST_HWM values (7, 'ess');
1 row inserted
SQL> insert into TEST_HWM values (8, 'es');
1 row inserted
SQL> insert into TEST_HWM values (9, 'es');
1 row inserted
SQL> insert into TEST_HWM values (10, 'es');
1 row inserted
5.2 刪除前rowid狀態
SQL> select id,
2 name,
3 rowid,
4 dbms_rowid.rowid_object(rowid) object_id,
5 dbms_rowid.rowid_relative_fno(rowid) file_id,
6 dbms_rowid.rowid_block_number(rowid) block_id,
7 dbms_rowid.rowid_row_number(rowid) num
8 from test_hwm;
ID NAME ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------ ----- ------------------ ---------- ---------- ---------- ----------
1 aa AAANAqAAHAAAAElAAA 53290 7 293 0
2 bb AAANAqAAHAAAAElAAB 53290 7 293 1
3 cc AAANAqAAHAAAAElAAC 53290 7 293 2
4 ds AAANAqAAHAAAAEmAAA 53290 7 294 0
5 dss AAANAqAAHAAAAEmAAB 53290 7 294 1
6 dss AAANAqAAHAAAAEmAAC 53290 7 294 2
7 ess AAANAqAAHAAAAEnAAA 53290 7 295 0
8 es AAANAqAAHAAAAEnAAB 53290 7 295 1
9 es AAANAqAAHAAAAEnAAC 53290 7 295 2
10 es AAANAqAAHAAAAEoAAA 53290 7 296 0
10 rows selected
5.3 刪除後rowid狀態
然後從table test_hwm中刪除一些資料：
SQL> delete from TEST_HWM where id = 2;
1 row deleted
SQL> delete from TEST_HWM where id = 3;
1 row deleted
SQL> delete from TEST_HWM where id = 4;
1 row deleted
SQL> delete from TEST_HWM where id = 7;
1 row deleted
SQL> delete from TEST_HWM where id = 8;
1 row deleted
SQL> select id,
2 name,
3 rowid,
4 dbms_rowid.rowid_object(rowid) object_id,
5 dbms_rowid.rowid_relative_fno(rowid) file_id,
6 dbms_rowid.rowid_block_number(rowid) block_id,
7 dbms_rowid.rowid_row_number(rowid) num
8 from test_hwm;
ID NAME ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------ ----- ------------------ ---------- ---------- ---------- ----------
1 aa AAANAqAAHAAAAElAAA 53290 7 293 0
5 dss AAANAqAAHAAAAEmAAB 53290 7 294 1
6 dss AAANAqAAHAAAAEmAAC 53290 7 294 2
9 es AAANAqAAHAAAAEnAAC 53290 7 295 2
10 es AAANAqAAHAAAAEoAAA 53290 7 296 0
從以上的資訊，可知表test_hwm中，剩下的資料是分佈在AAAAEl，AAAAEm，AAAAEn，AAAAEo這樣四個連續的block中。
SQL> exec show_space('TEST_HWM');
Total Blocks ..........................8
Total Bytes ..........................65536
Total MBytes ..........................0.0625
Unused Blocks ..........................0
Unused Bytes ..........................0
Unused KBytes ..........................0
Last Used Ext FileId....................7
Last Used Ext BlockId...................289
Last Used Block.........................8
The segment is analyzed below
FS1 Blocks (0-25) ....................0
FS2 Blocks (25-50) ....................1
FS3 Blocks (50-75) ....................3
FS4 Blocks (75-100) ....................1
Unformatted Blocks ....................0
Full Blocks ....................0
PL/SQL procedure successfully completed
通過show_space_assm我們可以看到目前這四個block的空間使用狀況，AAAAEl，AAAAEn，AAAAEo上各有一行資料，可以猜測free space為50-75%的3個block是這三個block，那麼free space為25-50%的1個block就是AAAAEm了，剩下free space為 75-100% 的3個block，是HWM下已格式化的尚未使用的block。
5.4 shrink後rowid狀態
SQL> alter table my_objects enable row movement;
Table altered
SQL> alter table my_objects shrink space;
Table altered
SQL> select id,
2 name,
3 rowid,
4 dbms_rowid.rowid_object(rowid) object_id,
5 dbms_rowid.rowid_relative_fno(rowid) file_id,
6 dbms_rowid.rowid_block_number(rowid) block_id,
7 dbms_rowid.rowid_row_number(rowid) num
8 from test_hwm;
ID NAME ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------ ----- ------------------ ---------- ---------- ---------- ----------
9 es AAANAqAAHAAAAEkAAA 53290 7 292 0
10 es AAANAqAAHAAAAEkAAB 53290 7 292 1
1 aa AAANAqAAHAAAAElAAA 53290 7 293 0
5 dss AAANAqAAHAAAAEmAAB 53290 7 294 1
6 dss AAANAqAAHAAAAEmAAC 53290 7 294 2
當執行了shrink操作後，可以發現shrink操作與move不太一樣。在move操作的時候，所有行的rowid都發生了變化，table所位於的block的區域也發生了變化，但是所有行物理儲存的順序都沒有發生變化，所以我們得到的結論是，oracle以block為單位，進行了block間的資料copy。而在shrink後，部分行資料的rowid發生了變化，同時，部分行資料的物理儲存的順序也發生了變化，而table所位於的block的區域卻沒有變化（ID為1，5，6的rowid沒有發生變化，ID為9，10兩行資料，原來在AAAAEn，AAAAEo上都移到AAAAEk上）。以上說明，shrink只移動了table其中一部分的行資料，來完成釋放空間，而且，這個過程是在table當前所使用的block中完成的。
6 shrink的注意點
1. move時產生的日誌比shrink時少．參看http://blog.csdn.net/huang_xw/article/details/7016365
2. shrink在移動行資料時，也一起維護了index上相應行的資料rowid的資訊，當然shrink過程中用來維護index的成本也會比較高。而表move後index的狀態是UNUSABLE的,需要進行rebuild。參見http://blog.csdn.net/huang_xw/article/details/7016415
3. oracle是從後向前移動行資料，那麼，shrink的操作就不會像move一樣，shrink不需要使用額外的空閒空間。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28894640/viewspace-764669/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle中shrink space命令詳解[轉]－－還示測試Oracle
- [Oracle] Shrink space & Table move比較Oracle
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- oracle10g shrink space 降低HWMOracle
- Oracle 10g Shrink Table 詳解Oracle 10g
- Oracle 11g alter table move與shrink spaceOracle
- oracle 10g__alter table shrink space compactOracle 10g
- oracle中imp命令詳解Oracle
- 【SHRINK】Oracle收縮表的詳細命令參考Oracle
- shrink space的最佳實踐
- Oracle IZ0-053 Q277(Table shrink space)Oracle
- ALTER TABLE MOVE | SHRINK SPACE區別
- 轉載-oracle Shrink命令的使用Oracle
- ALTER TABLE MOVE和SHRINK SPACE區別
- 【轉】Oracle：MOVE與SHRINK命令相比較Oracle
- Oracle OCP 1Z0 053 Q96(Shrink Space Compact)Oracle
- alter table move 與shrink space的區別
- alter table move跟shrink space的區別
- oracle shrinkOracle
- alter table move跟shrink space的區別（轉）
- alter table move 和 alter table shrink space的區別
- oracle shrink tableOracle
- shell中grep命令詳解
- java中jar命令詳解JavaJAR
- AIX中TOPAS命令詳解AI
- Oracle oradebug命令詳解Oracle
- oracle sqlplus命令詳解OracleSQL
- Linux中awk命令詳解Linux
- c#中命令模式詳解C#模式
- Linux中DD命令詳解Linux
- Linux中ps命令詳解Linux
- Java中jar命令用法詳解JavaJAR
- shell中set命令詳解(轉)
- oracle10g_alter table shrink space_compact_cascade回收空間測試(一)Oracle
- Oracle中的SCN詳解Oracle
- CentOS7中systemctl命令詳解CentOS
- shrink space 的整個過程就是在做delete和insertdelete
- Oracle中job的使用詳解Oracle