可以用來(lái)收縮段,消除空間碎片的方法有兩種:
創(chuàng)新互聯(lián)是一家專注網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷策劃、重慶小程序開(kāi)發(fā)、電子商務(wù)建設(shè)、網(wǎng)絡(luò)推廣、移動(dòng)互聯(lián)開(kāi)發(fā)、研究、服務(wù)為一體的技術(shù)型公司。公司成立10年以來(lái),已經(jīng)為1000多家成都搬家公司各業(yè)的企業(yè)公司提供互聯(lián)網(wǎng)服務(wù)?,F(xiàn)在,服務(wù)的1000多家客戶與我們一路同行,見(jiàn)證我們的成長(zhǎng);未來(lái),我們一起分享成功的喜悅。
1.alter table table_name move
需要注意:
1)move操作會(huì)鎖表。(如果是很小的表,可以在線做。如果是大表一定要注意,會(huì)長(zhǎng)時(shí)間鎖表,只能查詢,影響正常業(yè)務(wù)運(yùn)行。)
2)move操作會(huì)使索引失效,一定要rebuild。(因?yàn)閙ove操作會(huì)改變一些記錄的ROWID,所以MOVE之后索引會(huì)變?yōu)闊o(wú)效,需要REBUILD。)
2.使用shrink space
alter table table_name shrink space
前提條件
1) 必須啟用行記錄轉(zhuǎn)移(enable row movement)
2) 僅僅適用于堆表,且位于自動(dòng)段空間管理的表空間(堆表包括:標(biāo)準(zhǔn)表,分區(qū)表,物化視圖容器,物化視圖日志表)
優(yōu)點(diǎn):
提高緩存利用率,提高OLTP的性能
減少磁盤I/O,提高訪問(wèn)速度,節(jié)省磁盤空間
段收縮是在線的,索引在段收縮期間維護(hù),不要求額外的磁盤空間
加參數(shù)
cascade:縮小表及其索引,并移動(dòng)高水位線,釋放空間
compact:僅僅是縮小表和索引,并不移動(dòng)高水位線,不釋放空間
如果在業(yè)務(wù)繁忙時(shí)做壓縮,
可以使用alter table shrink space compact來(lái)對(duì)表格進(jìn)行碎片整理,而不調(diào)整高水位線,之后再次調(diào)用alter table table_name shrink space來(lái)釋放空間。
也可以使用alter table table_name shrink space cascade來(lái)同時(shí)對(duì)索引都進(jìn)行收縮,這等同于同時(shí)執(zhí)行alter index idxname shrink space。
方法一:move方式收縮表
1)創(chuàng)建一張新表test,并插入數(shù)據(jù)
SQL>
create
table
TEST (id
int
,
name
char
(2000)) tablespace users;
Table
created.
SQL>
insert
into
TEST
values
(1,
'aa'
);
SQL>
insert
into
TEST
values
(2,
'bb'
);
SQL>
insert
into
TEST
values
(3,
'cc'
);
--查看test表中rowid
SQL>
select
Dbms_Rowid.rowid_block_number(rowid)
from
TEST;
2) 刪除表中部分?jǐn)?shù)據(jù),并再次查看表中rowid
SQL>
delete
from
TEST
where
mod(id,2)=1;
SQL>
select
Dbms_Rowid.rowid_block_number(rowid)
from
TEST;
3) 對(duì)表執(zhí)行move操作
SQL>
alter
table
TEST
move
;
4)再次查看表中rowid
SQL>
select
Dbms_Rowid.rowid_block_number(rowid)
from
TEST;
小結(jié): 1
move操作后,數(shù)據(jù)的rowid發(fā)生了改變,我們知道,index是通過(guò)rowid來(lái)獲取數(shù)據(jù)行的,所以table上的index是必須要rebuild的。
5) 查看表中索引情況,此時(shí)索引為失效的
SQL> select index_name,status from user_indexes where index_name='TEST_INDEX';
INDEX_NAME STATUS
------------------------------ --------
TEST_INDEX UNUSABLE
6)在線重建索引
SQL>
alter
index
TEST_MOVE_INDEX rebuild online;
小結(jié):2
move操作后,表中索引會(huì)失效
--查看鎖情況
SQL> SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id;
SID USERNAME OBJECT_OWNER OBJECT_NAME LOCKED_MODE OS_USER_NAME
---------- -------------------- ---------------------- ------------------- ----------------------- -----------------------
33 YMM YMM TEST Exclusive (X) oracle
小結(jié):3
--Exclusive (X) 是6號(hào)鎖,獨(dú)占鎖。
--這就意味著,table在進(jìn)行move操作時(shí),我們只能對(duì)它進(jìn)行select的操作。
也就是說(shuō)當(dāng)我們的一個(gè)session對(duì)table進(jìn)行DML操作且沒(méi)有commit時(shí),在另一個(gè)session中是不能對(duì)這個(gè)table進(jìn)行move操作的,
否則oracle會(huì)返回這樣的錯(cuò)誤信息:ORA-00054 。
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
------------------------ ---------- ---------- --------
TEST 3 1280 10
--TEST表初始分配了10M的空間,1280個(gè)BLOCKS。
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
----------------------- ---------- -------------------
TEST
--USER_TABLES視圖顯示有0個(gè)使用的BLOCKS,1280個(gè)空閑BLOCKS。
--向表中插入數(shù)據(jù)
SQL> insert into TEST select * from information;
SQL> analyze table TEST compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
----------------------- ---------- ---------- ----------
TEST 3 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST 1006 274
--插入數(shù)據(jù)后,分配的空間仍不變,因?yàn)?0M還沒(méi)使用完。顯示使用了1006個(gè)BLOCKS,空閑274個(gè)BLOCKS。這時(shí)候的1006 BLOCKS即是高水位線。
SQL> commit;
SQL> select count(*) from test;
COUNT(*)
----------
122513
SQL> delete from test where rownum<=50000;
SQL> analyze table test compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
------------------------- --------------- ---------- ----------
TEST 3 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST 1006 274
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
573
--這邊可以看到,刪掉部分?jǐn)?shù)據(jù)后,仍然顯示使用了1006個(gè)BLOCKS,高水位沒(méi)變。但查詢真正使用的BLOCK數(shù)只有573個(gè)。所以DELETE操作是不會(huì)改變HWM的。
SQL> alter table TEST move;
SQL> analyze table TEST compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------------- -------------- ---------- ----------
TEST 3 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------- ---------- ------------
TEST 592 688
小結(jié):4
--MOVE之后,HWM降低了,空閑塊也上去了。
--但是分配的空間并沒(méi)有改變,仍然是1280個(gè)BLOCKS。
方法二:shrink space方式收縮表
SQL> delete from test where rownum<=50000;
--首先設(shè)置允許行遷移
SQL> alter table TEST enable row movement;
SQL> alter table TEST shrink space;
SQL> analyze table TEST compute statistics; -->使用analyze更新統(tǒng)計(jì)信息后EMPTY_BLOCKS得到數(shù)據(jù)
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
------------------------ ---------------- ---------- ----------
TEST 1 600 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST 592 8
--SHRINK SPACE真正做到了對(duì)段的壓縮,包括初始分配的也壓了,所以它是回收高水位線操作。
驗(yàn)證cascade與compact的差異
--刪除一些數(shù)據(jù)
SQL> delete from test where rownum<8000;
SQL> alter table test shrink space compact; -->使用compact方式收縮表段
SQL> exec show_space('TEST','SCOTT');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 2
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 103
Full Blocks ............................ 14,214 --僅有的變化為14318-14214=104塊,即完全填滿的數(shù)據(jù)塊減少了104塊
Total Blocks............................ 14,488 --數(shù)據(jù)的總塊數(shù)及總大小并沒(méi)有減少,即未移動(dòng)高水位線
Total Bytes............................. 118,685,696
Total MBytes............................ 113
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 16,521
Last Used Block......................... 147
PL/SQL procedure successfully completed.
SQL> alter table test shrink space cascade; -->使用cascade方式收縮
SQL> exec show_space('TEST','SCOTT');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 2
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 14,214
Total Blocks............................ 14,384 -->總塊數(shù)及總大小均已減少
Total Bytes............................. 117,833,728
Total MBytes............................ 112
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 16,521
Last Used Block......................... 44
PL/SQL procedure successfully completed.
-->收縮之后索引依然有效
SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='TEST';
OWNER INDEX_NAME STATUS
--------------- -------------------- ------------
SCOTT idx_test VALID
小結(jié):
compact:僅僅是縮小表和索引,并不移動(dòng)高水位線,不釋放空間
cascade:縮小表及其索引,并移動(dòng)高水位線,釋放空間
語(yǔ)法總結(jié):
ALTER TABLE <table_name> ENABLE ROW MOVEMENT -->前提條件
ALTER TABLE <table_name> SHRINK SPACE [ <NULL> | COMPACT | CASCADE ];
ALTER TABLE <table_name> SHRINK SPACE COMPCAT; -->縮小表和索引,不移動(dòng)高水位線,不釋放空間
ALTER TABLE <table_name> SHRINK SPACE; -->收縮表,降低高水位線;
ALTER TABLE <table_name> SHRINK SPACE CASCADE; -->收縮表,降低高水位線,并且相關(guān)索引也要收縮一下
ALTER TABLE <table_name> MODIFY LOB (lob_column) (SHRINK SPACE); -->收縮LOB段
ALTER INDEX IDXNAME SHRINK SPACE;
當(dāng)前名稱:oracle收縮表、清理碎片,釋放空間
網(wǎng)頁(yè)路徑:http://muchs.cn/article22/ihedjc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供虛擬主機(jī)、網(wǎng)站維護(hù)、網(wǎng)站內(nèi)鏈、網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化、軟件開(kāi)發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)