alter table move 和alter table shrink兩種重組表方法的對比

IT那活兒發布于2023-01-11 13:20 / 3447人閱讀

點擊上方“IT那活兒”，關注后了解更多精彩內容！！

01

move&shrink--空間釋放

shrink移動高水位線的同時，釋放申請的空間；而move不會。

1. 新建兩種表test3、test4，并向表中插入數據；

2. 查詢兩張表當前占用的數據塊數量；

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST3 11 88  .0625
TEST4 11 88  .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80   8
TEST4 80   8

3. 兩張分別刪除相同的數據量；

4. test3進行move操作，test4進行shrink操作；

SQL> select count(*) from test3;
COUNT(*)
----------
50000
SQL> delete from test3 where rownum<=20000;
20000 rows deleted.
SQL> analyze table test3 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST3 11 88 .0625
TEST4 11 88 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test3;
USED_BLOCKS
-----------
46
SQL> alter table test3 move;
Table altered.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> analyze table test3 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 80 8
SQL> delete from test4 where rownum<=20000;
20000 rows deleted.
SQL> analyze table test4 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 80 8
SQL> alter table test4 enable row movement;
Table altered.
SQL> alter table test4 shrink space;
Table altered.
SQL> analyze table test4 compute statistics;
Table analyzed.

5. 對比move和shrink的結果；

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 7 56  .0625
TEST3 7 56  .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52   4
TEST4 46  10

通過對比結果，兩張的初始數據量相同，刪除的數據量相同，剩余數據量也相同。但在分別執行move和shrink后，test3中的BLOCKS 數量明顯多于test4，而test4中EMPTY_BLOCKS多于test3.說明當前test4的空間釋放更完全。

02

move&shrink--索引

move后表中的索引需要重建；shrink自動維護索引。

1. 新建兩種表分別插入相同的數據（兩種表都建有索引）；

SQL> select rowid,id from test5;
ROWID ID
------------------ ----------
AAAVytAAEAAAC5jAAA 1
AAAVytAAEAAAC5jAAB 2
AAAVytAAEAAAC5jAAC 3
AAAVytAAEAAAC5jAAD 4
AAAVytAAEAAAC5lAAA 1
AAAVytAAEAAAC5lAAB 2
AAAVytAAEAAAC5lAAC 3
AAAVytAAEAAAC5lAAD 4
AAAVytAAEAAAC5lAAE 5
AAAVytAAEAAAC5lAAF 6
AAAVytAAEAAAC5lAAG 7
AAAVytAAEAAAC5lAAH 8
AAAVytAAEAAAC5lAAI 9
AAAVytAAEAAAC5lAAJ 10
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVytAAEAAAC5jAAA 11875
AAAVytAAEAAAC5jAAB 11875
AAAVytAAEAAAC5jAAC 11875
AAAVytAAEAAAC5jAAD 11875
AAAVytAAEAAAC5lAAA 11877
AAAVytAAEAAAC5lAAB 11877
AAAVytAAEAAAC5lAAC 11877
AAAVytAAEAAAC5lAAD 11877
AAAVytAAEAAAC5lAAE 11877
AAAVytAAEAAAC5lAAF 11877
AAAVytAAEAAAC5lAAG 11877
AAAVytAAEAAAC5lAAH 11877
AAAVytAAEAAAC5lAAI 11877
AAAVytAAEAAAC5lAAJ 11877

2. 刪除test5的部分數據后進行move操作；

SQL> delete from test5 where id >2;
10 rows deleted.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVytAAEAAAC5jAAA 11875
AAAVytAAEAAAC5jAAB 11875
AAAVytAAEAAAC5lAAA 11877
AAAVytAAEAAAC5lAAB 11877
SQL> alter table test5 move;
Table altered.

3. 對比move前后rowid，此時rowid已經發生了改變；

SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVywAAEAAAC5zAAA 11891
AAAVywAAEAAAC5zAAB 11891
AAAVywAAEAAAC5zAAC 11891
AAAVywAAEAAAC5zAAD 11891

4. 查看test5的索引狀態，當前已不可用，需要重建；

SQL> select index_name,status from user_indexes where index_name=T5;
INDEX_NAME STATUS
------------------------------ --------
T5 UNUSABLE

5. 重建test5的索引，索引恢復可用；

SQL> alter index t5 rebuild;
Index altered.
SQL> select index_name,status from user_indexes where index_name=T5;
INDEX_NAME STATUS
------------------------------ --------
T5 VALID

6. 刪除test6的部分數據后進行shrink操作；

SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAC 11867
AAAVysAAEAAAC5bAAD 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867
AAAVysAAEAAAC5bAAG 11867
AAAVysAAEAAAC5bAAH 11867
AAAVysAAEAAAC5bAAI 11867
AAAVysAAEAAAC5bAAJ 11867
AAAVysAAEAAAC5bAAK 11867
AAAVysAAEAAAC5bAAL 11867
AAAVysAAEAAAC5bAAM 11867
AAAVysAAEAAAC5bAAN 11867
SQL> delete from test6 where id >2;
10 rows deleted.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867

7. shrink后查看索引狀態和rowid的變化。

SQL> alter table test6 shrink space;
Table altered.
SQL> select index_name,status from user_indexes where index_name=T6;
INDEX_NAME STATUS
------------------------------ --------
T6 VALID
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867

通過對比實驗結果，shrink后表的rowid不變且當前索引依舊可用；但move后rowid產生了變化且索引不可用，需要重建恢復。

03

move&shrink--表空間不足

在表空間不充足時，move操作無法進行（需要跟原表相同的空間大小）。

1. 分別創建兩個大小相等的表空間；

SQL> create tablespace move datafile /oracle/files/move.dbf size 50M autoextend off;
Tablespace created.
SQL> create table test_move (id number) tablespace move;
Table created.
SQL> create tablespace shrink datafile /oracle/files/shrink.dbf size 50M autoextend off;
Tablespace created.

2. 在兩個表空間分別創建一張表；

SQL> create table test_move (id number) tablespace move;
Table created
SQL> create table test_shrink (id number) tablespace shrink;
Table created.

3. 向表中插入實驗數據；

SQL> declare
i number:=1;
begin
for i in 1..500000 loop
insert into scott.test_move (id) values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

4. 確認表1的大小，刪除部分數據后進行move操作；

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name=TEST_MOVE;
SUM(BYTES)/1024/1024
--------------------
31
SQL> delete from test_move where id < 2000;
9995 rows deleted.

5. 返回結果報錯，沒有充足的空間；

SQL> alter table test_move move;
alter table test_move move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace MOVE

6. 確認表2的大小，刪除部分數據后進行shrink操作；

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name=TEST_SHRINK;
SUM(BYTES)/1024/1024
--------------------
31
SQL> delete from test_shrink where id < 2000;
9995 rows deleted.
SQL> alter table test_shrink enable row movement;
Table altered.
SQL> alter table test_shrink shrink space;
Table altered.

7. 創建新的表空間，將表1move到新的表空間。

SQL> create tablespace move1 datafile /oracle/files/move1.dbf size 60M autoextend off;
Tablespace created.
SQL> alter table test_move move tablespace move1;
Table altered.

通過結果對比得，當所在的表空間剩余空間不足時，move是無法進行的，而shrink可以成功完成。但是可以將表move到其他空間充足的表空間進行重組，刪除原來的表再move會開始所在的表空間；而shrink無法實現，只能在當前所在的位置進行操作。

本文原創來源：IT那活兒微信公眾號（上海新炬王翦團隊）

云服務器 GPU云服務器 altered alter Table使用方法 js重制table數據方法

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129697.html

大廠面試預備篇——《兩萬字MySql基礎總結》??建議收藏

?? 一條獨家專欄 ?? 搞技術，進大廠，聊人生 ?《大廠面試突擊》——面試10多家中大廠的萬字總結 ?《技術專家修煉》——高薪必備，企業真實場景 ?《leetcode 300題》——每天一道算法題，進大廠必備 ?《糊涂算法》——數據結構+算法全面講解 ?《從實戰學python》——python的各種應用 ?《程序人生》——聽一條聊職場，聊人生 ?更多資料點這里天下難事，必作于易；天下大事，...

dreamtecher 2021-09-07 09:59 評論0 收藏0

發表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1346·2023-01-11 13:20
RAC雙節點crash回復一例

閱讀 1684·2023-01-11 13:20
ORA-600處理一例

閱讀 1132·2023-01-11 13:20
雙節點RAC實例2 HANG 故障分析一例

閱讀 1859·2023-01-11 13:20
RAC集群節點1重啟分析一例

閱讀 4100·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1385·2023-01-11 13:20
分布式緩存組件故障分析及監控優化

閱讀 3597·2023-01-11 13:20

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

alter table move 和alter table shrink兩種重組表方法的對比

01

02

03

本文原創來源：IT那活兒微信公眾號（上海新炬王翦團隊）

相關文章

大廠面試預備篇——《兩萬字MySql基礎總結》??建議收藏

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節點crash回復一例

ORA-600處理一例

雙節點RAC實例2 HANG 故障分析一例

RAC集群節點1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監控優化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

alter table move 和alter table shrink兩種重組表方法的對比

01

02

03

本 文 原 創 來 源：IT那活兒微信公眾號（上海新炬王翦團隊）

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

本文原創來源：IT那活兒微信公眾號（上海新炬王翦團隊）