Oracle資料表碎片整理

笱局長發表於2018-12-10
Oracle

資料表的增刪改總是避免不了產生碎片的問題，在Oracle引入表空間本地管理和ASSM之後，極端情況下，明明表空間使用率不高，需要入庫的資料庫物件也不大，但就是報錯

ORA-01653: unable to extend table BAIYANG.TEST01 by 128 in tablespace TBS_BAIYANG

這時需要定位是否有碎片引起

（一）建立測試環境


sys@ORCL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

# 建立表空間，目標表空間tbs_baiyang預設本地管理
create tablespace tbs_baiyang datafile `/u01/app/oracle/oradata/standby/datafile/tbs_baiyang.dbf` size 20m;

sys@ORCL> select TABLESPACE_NAME,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT,CONTENTS from dba_tablespaces where TABLESPACE_NAME = upper(`tbs_baiyang`);


TABLESPACE_NAME                EXTENT_MAN SEGMEN CONTENTS
------------------------------ ---------- ------ ---------
TBS_BAIYANG                    LOCAL      AUTO   PERMANENT


# 建立表
create table baiyang.test01 tablespace tbs_baiyang as select * from all_objects;

# 收集表資訊
exec dbms_stats.gather_table_stats(`BAIYANG`,`TEST01`,cascade => true);

# 檢視錶的狀態，系統一共分配10M空間 --1280(block)*8k，其中HWM 1233

sys@ORCL> @hwm
Enter value for tab_name: TEST01
Enter value for tab_name: TEST01
Enter value for owner: BAIYANG
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
TEST01                                     46           1280           1233

# 表空間分配情況，使用率55%左右，統計資訊並不是很精確
SQL> @tbs2

                  Free    Largest   Total   Available Pct
   Tablespace     Frags   Frag (MB)   (MB)     (MB)    Used
---------------- -------- --------- -------- --------- ----
SYSAUX                  2        36      730        36   95
UNDOTBS1                9        32       95        35   63
TBS_BAIYANG             1         9       20         9   55
USERS                   2         6      119         6   95
SYSTEM                  2         7      760         8   99
LXX                     1         9       10         9   10
                 --------           -------- ---------
sum                    17              1,734       103

（二）目前來看一切正常，做些更新操作

# 刪除部分資料
SQL>  delete from baiyang.test01 where  mod(object_id,3) = 0;

28361 rows deleted.

SQL> commit;

Commit complete.

# 收集表資訊
exec dbms_stats.gather_table_stats(`BAIYANG`,`TEST01`,cascade => true);

（三）檢視當前表、表空間的使用情況

# 首先檢視錶狀態，HWM沒有變化
SQL> @hwm
Enter value for tab_name: test01
Enter value for tab_name: test01
Enter value for owner: baiyang
Enter value for tab_name: TEST01
Enter value for tab_name: TEST01
Enter value for owner: BAIYANG
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
TEST01                                     46           1280           1233

PL/SQL procedure successfully complet

# 檢視錶空間的使用情況，和之前沒有變化
SQL> @tbs2

                  Free    Largest   Total   Available Pct
   Tablespace     Frags   Frag (MB)   (MB)     (MB)    Used
---------------- -------- --------- -------- --------- ----
SYSAUX                  2        36      730        36   95
UNDOTBS1               10        27       95        29   70
TBS_BAIYANG             1         9       20         9   55
USERS                   2         6      119         6   95
SYSTEM                  2         7      760         8   99
LXX                     1         9       10         9   10
                 --------           -------- ---------
sum                    18              1,734        97

我們知道已經刪除了1/3的資料，表實際使用的空間將降低1/3，但是在收集表資訊之後，各項資料沒有變化，這時就要解決表碎片的問題
（四）解決表碎片的問題

alter table baiyang.test01 enable row movement; 
# 壓縮表並下調高水位
alter table baiyang.test01 shrink space cascade; 
alter table baiyang.test01 disable row movement;

# 檢視錶狀態。HWM下降
sys@ORCL> @hwm
Enter value for tab_name: TEST01
Enter value for tab_name: TEST01
Enter value for owner: BAIYANG
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
TEST01                                      6            832            825

# 檢視標間使用情況，使用率降低至38%
                   Free    Largest   Total   Available Pct
   Tablespace     Frags   Frag (MB)   (MB)     (MB)    Used
---------------- -------- --------- -------- --------- ----
SYSAUX                  2        36      730        36   95
UNDOTBS1               10        21       95        23   76
TBS_BAIYANG             1        13       20        13   38
USERS                   2         6      119         6   95
SYSTEM                  2         7      760         8   99
LXX                     1         9       10         9   10
                 --------           -------- ---------
sum                    18              1,734        94

資料表的碎片使用shrink/move都可以達到清理的效果，shrink支援線上，move需要重建索引等，根據需要自由選擇。

如果對一個正在執行的生產環境，怎麼才能知道資料庫的碎片化程度，哪些物件存在碎片呢？


相關文章