Oracle中shrink space命令
shrink_clause：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm#i2192484
首先oracleshrink是10g之后才引出的，有shrinktable和shrinkspace兩種，這里介紹shrinkspace
壓縮分兩個階段：
1、數(shù)據(jù)重組：這個過程是通過一系列的insertdelete操作，將數(shù)據(jù)盡量排在列的前面進(jìn)行重新組合。
2、HWM調(diào)整：這個過程是對HWM的調(diào)整，釋放空閑數(shù)據(jù)庫。
PS：shrink之間必須開啟行移動功能
altertabletable_nameenablerowmovement;
基本語法：
altertable<table_name>shrinkspace[<null>|cascade|compact];
--altertable<table_name>shrinkspacecompact;
只收縮表，這個實際上是只執(zhí)行了第一個階段，HWM保持不變。
--altertable<table_name>shrinkspacecascade;
收縮表并且相關(guān)索引也會被收縮，HWM會降低
--altertable<table_name>shrinkspace;
收縮表，降低HWM(HighWaterMark)
限制條件：
1、不能對cluster、clustered table 或者任何有LONG列的對象使用這個語句
2、壓縮段不支持有函數(shù)索引、位圖鏈接索引的表
3、這語句不能壓縮二級索引表的映射表，即使設(shè)置了CASCADE
4、不能對壓縮表使用該語句
5、不能壓縮on commit 類型的物化視圖的主表，rowid物化視圖必須在壓縮操作之后重建
詳解：
oracle10g開始提供shrink的命令，要求表空間是自動段空間管理（ASSM），降低HWM。
segment shrink 分為兩個階段：
1、數(shù)據(jù)重組（compact）：通過一系列insert、delete操作，將數(shù)據(jù)盡量排在段的前面，這個過程中需要在表上加RX鎖，及只需要移動的行上加鎖。由于涉及到rowid的改變，需要enable row movement。同時要distable基于rowid的triggers，這個過程對業(yè)務(wù)影響比較小（--由于采用compact，只有涉及移動的行才加鎖，所以不會鎖定整個表，其他的DML操作有部分可以進(jìn)行，進(jìn)而減小系統(tǒng)高峰期的性能開銷）
2、HWM調(diào)整：第二階段是調(diào)整HWM位置，釋放空閑數(shù)據(jù)塊，此過程需要在表上加X鎖（獨享鎖，因此這個表都被鎖定，如果系統(tǒng)處在高峰期的時候，其他在此表的DML被掛起，會產(chǎn)生嚴(yán)重阻塞），會造成表上所有DML語句阻塞，系統(tǒng)忙時影響較大，
鎖的內(nèi)容參考：http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm
shrink space語句兩個階段都執(zhí)行
shrink space compact語句只執(zhí)行第一個階段。
在業(yè)務(wù)繁忙的時候，可以先執(zhí)行shrink space compact重組數(shù)據(jù)，然后不滿的時候執(zhí)行shrink space降低HWM釋放空閑數(shù)據(jù)塊。
shrink必須開啟對象的row movement功能（shrink index 不需要），alter table table_name enable row movement.但是要注意，該語句會造成引用table_name的對象（如存儲過程、包、試圖等）變?yōu)闊o效，執(zhí)行完最好由utlrp.sql來編譯無效對象。
shrink不會使表的索引失效。但是move會，因此，move后必須重建索引，（alter table table_name move;alter index index_name rebuild）
語法：
alter table shrink space[|cpmpact|cascade];
alter table shrink space compcat; 把塊中的數(shù)據(jù)堆到一起，但會保持high water mark
alter table shrink space;收縮表，降低high water mark
alter table shrink space cascade;收縮表，降低high water mark，并相關(guān)索引也要收縮。
alter index indexname shrink space;收縮索引
補(bǔ)充：
--編譯無效對象腳本utlrp.sql
>$ sqlplus /no log
SQL>connect sys/pwd@sid AS sysdba
SQL>@?/rdbms/admin/utlrp.sql
utlrp.sql腳本可以在數(shù)據(jù)庫運行的狀態(tài)下執(zhí)行以編譯、數(shù)據(jù)庫中的invalid對象.
oracle建議在對數(shù)據(jù)庫進(jìn)行遷移、升級、降級后都運行一遍utlrp.sql以編譯無效對象。
--打完patch后重建數(shù)據(jù)字典視圖腳本catpatch.sql
為了保證系統(tǒng)的的數(shù)據(jù)詞典的完整性和有效性，最好的打patch后在migrate狀態(tài)下運行catpatch.sql
sql> shutdown immediate
sql> startup migrate
sql> @?/rdbms/admin/catpatch.sql
sql> shutdown immediate
sql> startup
為加快速度，可臨時調(diào)大這２個參數(shù)：
show parameter shared_pool_size
show parameter large_pool_size
1.1 創(chuàng)建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 刪除后的信息
然后我們隨機(jī)地從table MY_OBJECTS中刪除一部分?jǐn)?shù)據(jù)：
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的現(xiàn)有數(shù)據(jù)行進(jìn)行重組。
4 shink操作
要使用assm上的shink，首先我們需要使該表支持行移動，可以用這樣的命令來完成：
SQL> alter table my_objects enable row movement;
Table altered
現(xiàn)在，就可以來降低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
在執(zhí)行玩shrink命令后，此時表my_objects的HWM現(xiàn)在降到了276(280 - 5 + 1)的位置，而且HWM下的block的空間使用狀況，full space的block有259個，free space 為25-50% 和50-75% Block只有1個。
5 shrink space原理剖析
5.1 實驗環(huán)境
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狀態(tài)
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狀態(tài)
然后從table test_hwm中刪除一些數(shù)據(jù)：
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中，剩下的數(shù)據(jù)是分布在AAAAEl，AAAAEm，AAAAEn，AAAAEo這樣四個連續(xù)的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上各有一行數(shù)據(jù)，可以猜測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狀態(tài)
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
當(dāng)執(zhí)行了shrink操作后，可以發(fā)現(xiàn)shrink操作與move不太一樣。在move操作的時候，所有行的rowid都發(fā)生了變化，table所位于的block的區(qū)域也發(fā)生了變化，但是所有行物理存儲的順序都沒有發(fā)生變化，所以我們得到的結(jié)論是，oracle以block為單位，進(jìn)行了block間的數(shù)據(jù)copy。而在shrink后，部分行數(shù)據(jù)的rowid發(fā)生了變化，同時，部分行數(shù)據(jù)的物理存儲的順序也發(fā)生了變化，而table所位于的block的區(qū)域卻沒有變化（ID為1，5，6的rowid沒有發(fā)生變化，ID為9，10兩行數(shù)據(jù)，原來在AAAAEn，AAAAEo上都移到AAAAEk上）。以上說明，shrink只移動了table其中一部分的行數(shù)據(jù)，來完成釋放空間，而且，這個過程是在table當(dāng)前所使用的block中完成的。
6 shrink的注意點
1. move時產(chǎn)生的日志比shrink時少．參看http://blog.csdn.net/huang_xw/article/details/7016365
2. shrink在移動行數(shù)據(jù)時，也一起維護(hù)了index上相應(yīng)行的數(shù)據(jù)rowid的信息，當(dāng)然shrink過程中用來維護(hù)index的成本也會比較高。而表move后index的狀態(tài)是UNUSABLE的,需要進(jìn)行rebuild。參見http://blog.csdn.net/huang_xw/article/details/7016415
3. oracle是從后向前移動行數(shù)據(jù)，那么，shrink的操作就不會像move一樣，shrink不需要使用額外的空閑空間。
總結(jié)
以上是生活随笔為你收集整理的Oracle中shrink space命令的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ARP欺骗与MITM（中间人攻击）实例
- 下一篇： 读取Exchange的用户未读邮件数3种