表异常增大的bug

今天突然发现一个表空间的空间不足，后台开始报错：ORA-1653。

奇怪的是，上周才检查过表空间的容量，这个表空间还有20G左右的空间，怎么这么快就用完了。

检查了是哪个表占用了大量的空间：

SQL> SELECT * FROM                                                  ITPUB个人空间A2|
[*Mxa
  2  (   ITPUB个人空间
|6P3wN"Y&OL%R6Q
  3  SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 G   ITPUB个人空间b(M5j:Se
  4  FROM DBA_SEGMENTS   
aU)m_0t8n-`7199859  5  WHERE WNER = 'ZHEJIANG'ITPUB个人空间Q"zZ6E o?'ATr0T
  6  GROUP BY SEGMENT_NAME
!l'].{
|l7199859  7  ORDER BY 2 DESCITPUB个人空间}[K(AUT~]
  8  )
6[*K-[8He*G[I7199859  9  WHERE ROWNUM < 5;

SEGMENT_NAME                              GITPUB个人空间e*}T6NkGo
-------------------------------- ----------
S"iK8I
Q,{v-x(l7199859ORD_ORDER_RECEIVE                21.3242188ITPUB个人空间rG;LpJ7v
ORD_HIT_COMM                     5.47070313ITPUB个人空间 _6wO2u}~x
CAT_LOG_AREA_PRICE                   5.0625
bjbNo"G`1V]7199859ORD_ORDER_ITEM                   3.19335938

问题对象已经找到，这个表的空间占用了21g，而正常情况下，这个表应该不会超过3个G。

检查表的数据量是否发生数据量级的增长：

SQL> SELECT COUNT(*) FROM ZHEJIANG.ORD_ORDER_RECEIVE;

  COUNT(*)ITPUB个人空间u,N.ta/U9_/Z
hA
----------ITPUB个人空间+e0N)Oh8Qrh6@
mS
   4141294

总共四百万条记录，这个数据量也很正常，再看看表的记录长度：

SQL> SELECT AVG_ROW_LEN FROM DBA_TABLES
bzy1[L7_nl7199859  2  WHERE TABLE_NAME = 'ORD_ORDER_RECEIVE'
~e)e1W#Z|S*N7199859  3  AND WNER = 'ZHEJIANG';

AVG_ROW_LEN
LA ]LJ%fl7199859-----------

aE)FgsYPW7199859        323

这个平均长度也没有问题，如果根据表的大小除以表记录数计算来计算平均长度：

SQL> select 21.3242188*1024*1024*1024/4136937 from dual;

21.3242188*1024*1024*1024/4136937
q)F2CK|M~N7199859---------------------------------ITPUB个人空间q*?]-l;XJ
                       5534.70009

这个长度就离谱了，计算这个表的最大长度：

SQL> SELECT SUM(DATA_LENGTH) FROM DBA_TAB_COLUMNSITPUB个人空间^1?#?g"]G1wrH
  2  WHERE WNER = 'ZHEJIANG'ITPUB个人空间AZMD'O9m5Pp hI
  3  AND TABLE_NAME = 'ORD_ORDER_RECEIVE';

SUM(DATA_LENGTH)
1o]x&A'OX%RF7199859----------------ITPUB个人空间"k9o5`LCR;ay2bf1@
             777

最大长度才777，可是现在这张表的平均大小已经到了5K多，看来表中存在大量没有使用的BLOCK。

SQL> SELECT INDEX_NAME,ITPUB个人空间]nJg3}1R
  2     SUM(S.BYTES)/1024/1024 MITPUB个人空间)LG,G
e]A u
  3  FROM DBA_INDEXES I, DBA_SEGMENTS S
4~B'bSo dX-d7199859  4  WHERE I.OWNER = 'ZHEJIANG'
[&W2R4q%[Um[7199859  5  AND S.OWNER = 'ZHEJIANG'ITPUB个人空间U!k[H"@ a{OX
  6  AND SEGMENT_TYPE = 'INDEX'ITPUB个人空间E4@(@?1E#ca\
  7  AND SEGMENT_NAME = INDEX_NAME
m7mIIWxu@(cz7199859  8  AND TABLE_NAME = 'ORD_ORDER_RECEIVE'ITPUB个人空间G:p B{{ ?
  9  GROUP BY INDEX_NAME;

INDEX_NAME                              M
)js2@~L'T&b7199859------------------------------ ----------ITPUB个人空间9dO-_v9P*W
TU_ORD_ORDER_COMB_ITEM_FLAG           224ITPUB个人空间!q(V Hm$t${j
TU_ORD_ORDER_RECEIVE_PROD_ID          204ITPUB个人空间Ys7YW8C8Uw
IND_ORD_ORDER_REC_REC_DATE            108
W5xc5yc Ov7199859PK_ORD_ORDER_RECEIVE                  196
g:D!l*bS:OT7199859IND_ORD_RECEIVE_BUYERSENDER           340

索引的大小都很正常，看来问题只是出在了表上，下面通过两个DBMS_SPACE来检查表的block使用情况，这里对两个过程进行了简单的封装：

SQL> create or replace procedure p_unused_space(p_object_name in varchar2,ITPUB个人空间9~q+v/j#i@
  2     p_object_type in varchar2 default 'TABLE',ITPUB个人空间I#\ s^!w ?4j;p
  3     p_owner in varchar2 default user,
,R:S+f4E o)B7199859  4     p_partition_name in varchar2 default '') isITPUB个人空间6bR;Z \4L9w
  5     v_total_blocks number;
!IP^,x,[0d(?7199859  6     v_total_bytes number;
wv)B/_D$gW$h7199859  7     v_unused_blocks number;ITPUB个人空间#gv%J%\9Bq
rT
  8     v_unused_bytes number;ITPUB个人空间)h8X4YT#X:QjL7{T
  9     v_last_used_extent_file_id number;ITPUB个人空间9E X7S,e3?'C.o+Dsp
 10     v_last_used_extent_block_id number;
1U,jk w1nQ |l7199859 11     v_last_used_block number;
r0eA6|jh'U1^&H1m3e7199859 12  beginITPUB个人空间6^ZN8B Ux
 13     dbms_space.unused_space(upper(p_owner), upper(p_object_name), upper(p_object_type), v_total_blocks,ITPUB个人空间*t!Pi2jA,mJ"l
 14             v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
(\t,eic"I XUw7199859 15             v_last_used_extent_block_id, v_last_used_block, upper(p_partition_name));ITPUB个人空间js+vnl,x@
 16     dbms_output.put_line('total_blocks is ' || v_total_blocks);ITPUB个人空间H)X!Xsde1eH:k
 17     dbms_output.put_line('total_bytes is ' || v_total_bytes);ITPUB个人空间E9YuMd
 18     dbms_output.put_line('unused_blocks is ' || v_unused_blocks);ITPUB个人空间X8a:x J.@bK.ECv
 19     dbms_output.put_line('unused_bytes is ' || v_unused_bytes);ITPUB个人空间Wi&DkQ
 20     dbms_output.put_line('last_used_extent_file_id is ' || v_last_used_extent_file_id);
.g8Bbs4_5n&]#q9NSd7199859 21     dbms_output.put_line('last_used_extent_block_id is ' || v_last_used_extent_block_id);
5q vP8{_:F4ovNf7199859 22     dbms_output.put_line('last_used_block is ' || v_last_used_block);ITPUB个人空间kOu'v8@K*z7F+hWm
 23  end;
"\m4?7?G8B7s7199859 24  /

过程已创建。

SQL> EXEC P_UNUSED_SPACE('ORD_ORDER_RECEIVE', 'TABLE', 'ZHEJIANG')
0VYON#M6s7199859total_blocks is 1397504
F'IG^H1OC9kX7199859total_bytes is 22896705536ITPUB个人空间V8Zz*ft
unused_blocks is 0
2PciZmq7199859unused_bytes is 0
$M+\:D$P*L!bj5j7199859last_used_extent_file_id is 31ITPUB个人空间v0G(zqrdC
last_used_extent_block_id is 110981ITPUB个人空间4@j*v2QX"mx%F4_F(?
last_used_block is 128

PL/SQL过程已成功完成。

SQL> create or replace procedure p_space_usage (p_segment_name in varchar2,ITPUB个人空间8I2\+nV#mPX
  2      p_segment_type in varchar2 default 'TABLE',ITPUB个人空间Se,LGp+v:q u.MOy
  3     p_segment_owner in varchar2 default user,
i8h0{JC_&^*d7199859  4     p_partition_name in varchar2 default '') as
e6_+Y}4_]BR Z7199859  5     v_unformatted_blocks number;ITPUB个人空间nvM)m` Z5{w
  6     v_unformatted_bytes number;
O2Xa`#vu
YNMH[7199859  7     v_fs1_blocks number;ITPUB个人空间l e1m zt pO
  8     v_fs1_bytes number;ITPUB个人空间mx2_-W$l#G;J1X
  9     v_fs2_blocks number;
1XI5x,W'g-i/@0P[7199859 10     v_fs2_bytes number;ITPUB个人空间.Y4E.[Y m-Fz+A
 11     v_fs3_blocks number;
4V7V[-}"x`7199859 12     v_fs3_bytes number;
YBH
~_,H7199859 13     v_fs4_blocks number;ITPUB个人空间}-\m&p*cM#q%`| ?+A
 14     v_fs4_bytes number;
;_2e KiW_7199859 15     v_full_blocks number;
"z| Zk'd/Xy7199859 16     v_full_bytes number;
B"o
YU-K,|3Q7199859 17  begin
nF9^$H6t7199859 18     dbms_space.space_usage(upper(p_segment_owner), upper(p_segment_name), upper(p_segment_type), v_unformatted_blocks,
+?FZ9Dwfo#g7199859 19             v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
c.aW
n,S],_7199859 20             v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, upper(p_partition_name));ITPUB个人空间[9sv _p;Ea$K
 21    
`tVdKqAS3y7199859 22     dbms_output.put_line('unformatted_blocks is ' || v_unformatted_blocks);
ut-KxhaEM7199859 23     dbms_output.put_line('unformatted_bytes is ' || v_unformatted_bytes);ITPUB个人空间kV@B'S&f
 24     dbms_output.put_line('fs1_blocks is ' || v_fs1_blocks);ITPUB个人空间E1K7U:jJ;`+Z
kH/S'L
 25     dbms_output.put_line('fs1_bytes is ' || v_fs1_bytes);
|/B C,C_T7199859 26     dbms_output.put_line('fs2_blocks is ' || v_fs2_blocks);
7K*^z:MX4hNX,O7M7199859 27     dbms_output.put_line('fs2_bytes is ' || v_fs2_bytes);ITPUB个人空间(`)DZu6~.^'|
 28     dbms_output.put_line('fs3_blocks is ' || v_fs3_blocks);ITPUB个人空间4l]g.a&L;} qbb
 29     dbms_output.put_line('fs3_bytes is ' || v_fs3_bytes);
;S*a:orf7199859 30     dbms_output.put_line('fs4_blocks is ' || v_fs4_blocks);
*Wv&d*H&Jw6Ka7199859 31     dbms_output.put_line('fs4_bytes is ' || v_fs4_bytes);
V1q)o7y6K&Pl:RY7199859 32     dbms_output.put_line('full_blocks is ' || v_full_blocks);
.|8JV-fwy7199859 33     dbms_output.put_line('full_bytes is ' || v_full_bytes);
h9hVE1|tD E5r7199859 34  end;ITPUB个人空间
{&I$T1i1A)TO
 35  /

过程已创建。

SQL> EXEC P_SPACE_USAGE('ORD_ORDER_RECEIVE', 'TABLE', 'ZHEJIANG');ITPUB个人空间6}]{(}cP6mI
unformatted_blocks is 1300462
0`6cH.E/^r0nx~F7199859unformatted_bytes is 21306769408ITPUB个人空间H0L-deG }&A
w
fs1_blocks is 37
{zj/d9?)a/X^n @7199859fs1_bytes is 606208
,ZSp ^5@hia7199859fs2_blocks is 52ITPUB个人空间c5fUAc/D
fs2_bytes is 851968ITPUB个人空间Qe6s+qn
fs3_blocks is 22ITPUB个人空间#D"E0S/y%bq
fs3_bytes is 360448ITPUB个人空间-H.d8r}n
fs4_blocks is 903
|Q}-Ijw9@K}7199859fs4_bytes is 14794752ITPUB个人空间/{z2g7M:@
XY%^7@SD
full_blocks is 94412
%j$xT0qafv`7199859full_bytes is 1546846208

PL/SQL过程已成功完成。

现在问题已经很清晰了，表中没有被格式化的block大小就有20G左右，正式这个导致了问题的产生。

根据上面的信息查询metalink，找到了相关问题的描述：Bug No. 5987262，这篇文档的标题是：TABLESPACE IS ABNORMALLY INCREASED BY UNFORMATTED BLOCKS。这篇文章中描述的情况与当前的问题十分相似，不过这篇文章描述的bug信息被认为是重复的bug，基础bug指向Bug No. 5890312。

而bug 5890312的文档标题为：HANG OBSERVED WHILE CREATING CTXCAT INDEX，且版本信息为11.1.0.0，怎么看似乎都和当前的问题无关。

开始的时候怀疑Oracle再指向基础bug的时候出现了错误，于是继续查找类似的问题，结果发现类似的问题的基础bug都指向Bug No. 5890312。

仔细阅读了一下这个bug的描述，发现这个bug果然没有指错。虽然表现出来的现象和当前问题不一样，但是本质都是一样的，Oracle再分配空间的时候出现了泄漏，造成了大量的unformatted_blocks。

这个Bug在11g将被解决，而且Oracle提供了针对10.2.0.3和9.2.0.8的补丁来修正这个bug。

短时间内无法停机打补丁，可以先通过ALTER TABLE MOVE来临时解决这个问题。