oracle 收縮表、清理碎片，釋放空間

發(fā)布時(shí)間：2020-08-13 14:23:03 來(lái)源：ITPUB博客閱讀：952 作者：yangmm 欄目：關(guān)系型數(shù)據(jù)庫(kù)

可以用來(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的性能

減少磁盤(pán)I/O，提高訪問(wèn)速度，節(jié)省磁盤(pán)空間

段收縮是在線的，索引在段收縮期間維護(hù)，不要求額外的磁盤(pán)空間

加參數(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;

向AI問(wèn)一下細(xì)節(jié)

oracle 收縮表、清理碎片，釋放空間

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽

oracle 收縮表、清理碎片，釋放空間