天天看点

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需要重建索引等,根据需要自由选择。

如果对一个正在运行的生产环境,怎么才能知道数据库的碎片化程度,哪些对象存在碎片呢?