表异常增大的bug

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

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

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

SQL> SELECT * FROM                                                  
0h+p$v*acP9C
x2q?:d(V0  2  (   ITPUB个人空间(E#U L7c-[6hK
h
  3  SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 G   ITPUB个人空间 w8P&FxxbY1_ Zm
  4  FROM DBA_SEGMENTS   ITPUB个人空间.Ktd&j(I*utS
  5  WHERE WNER = 'ZHEJIANG'ITPUB个人空间E)o)};w}e"hO
  6  GROUP BY SEGMENT_NAME
'D*Q4A_+E*?0  7  ORDER BY 2 DESCITPUB个人空间``qzM;Dqe
  8  )ITPUB个人空间+i"@#o|$DT)~
  9  WHERE ROWNUM < 5;

SEGMENT_NAME                              G
wf/c rm OaC&o6@2^0-------------------------------- ----------ITPUB个人空间/h0rn?7LTY%W-}
ORD_ORDER_RECEIVE                21.3242188
&N7Dr} ^0zy}!? `0ORD_HIT_COMM                     5.47070313
C0t}U})Yg6Cb;l-k0CAT_LOG_AREA_PRICE                   5.0625
!T;Jaqpo/gi0ORD_ORDER_ITEM                   3.19335938

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

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

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

  COUNT(*)ITPUB个人空间v;I
q%kZ
----------
g(a2U"w#[O-Z0   4141294

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

SQL> SELECT AVG_ROW_LEN FROM DBA_TABLES
JT;}4Fu0  2  WHERE TABLE_NAME = 'ORD_ORDER_RECEIVE'ITPUB个人空间4?Bdd#S5[G~`
  3  AND WNER = 'ZHEJIANG';

AVG_ROW_LENITPUB个人空间#D)x$ut+jC
-----------
1|,^ziK2YW!A0        323

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

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

21.3242188*1024*1024*1024/4136937ITPUB个人空间y j/pM0S&K!u
---------------------------------
$h
zI6y\k}:Gr4^`Tg6X0                       5534.70009

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

SQL> SELECT SUM(DATA_LENGTH) FROM DBA_TAB_COLUMNSITPUB个人空间*\.~pK)r#l8H
  2  WHERE WNER = 'ZHEJIANG'
I3_DA MJ0  3  AND TABLE_NAME = 'ORD_ORDER_RECEIVE';

SUM(DATA_LENGTH)
9@@$SW9e*jwZ0----------------
E`iQ'K S7HX0             777

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

SQL> SELECT INDEX_NAME,ITPUB个人空间:r7X[XP@q
  2     SUM(S.BYTES)/1024/1024 M
R"e@2M5^0  3  FROM DBA_INDEXES I, DBA_SEGMENTS SITPUB个人空间z
V9Q"y&`:XJ zdyJm
  4  WHERE I.OWNER = 'ZHEJIANG'
-h%F Y:[h ^[U0  5  AND S.OWNER = 'ZHEJIANG'ITPUB个人空间p$d k4m#u6Dq2z
  6  AND SEGMENT_TYPE = 'INDEX'ITPUB个人空间x8qa3m eZ+@jZt
  7  AND SEGMENT_NAME = INDEX_NAMEITPUB个人空间b'V,~#d,d.H J:~ Y
  8  AND TABLE_NAME = 'ORD_ORDER_RECEIVE'
N`3Vk;r4uI-s0  9  GROUP BY INDEX_NAME;

INDEX_NAME                              MITPUB个人空间Wo/U ZvPX)V
------------------------------ ----------
7@W*l%Q_%c*d*n v0TU_ORD_ORDER_COMB_ITEM_FLAG           224ITPUB个人空间#l8@4Ewg2Fm6?l
TU_ORD_ORDER_RECEIVE_PROD_ID          204
f-EIDA!\Mc0IND_ORD_ORDER_REC_REC_DATE            108
!{3X&{*\-}nDs@$?+Nw0PK_ORD_ORDER_RECEIVE                  196ITPUB个人空间3i3gOX&m[)KU
IND_ORD_RECEIVE_BUYERSENDER           340

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

SQL> create or replace procedure p_unused_space(p_object_name in varchar2,
s4l4n%x/m Z$s,@*?3y0  2     p_object_type in varchar2 default 'TABLE',ITPUB个人空间DzJ4b^2s3~n~
  3     p_owner in varchar2 default user,ITPUB个人空间bP
kUa
  4     p_partition_name in varchar2 default '') isITPUB个人空间Ry/bbE
D&Rw
  5     v_total_blocks number;
I/M-}/|i4W3Y0  6     v_total_bytes number;
*~"j%dy}n#?n0  7     v_unused_blocks number;
;~(Jj
H1T/g p"I0  8     v_unused_bytes number;

O|z9c4|}l0  9     v_last_used_extent_file_id number;ITPUB个人空间{h(x4`,n7C:T@9U
 10     v_last_used_extent_block_id number;ITPUB个人空间3M*s7Cf:Ol-b1R
 11     v_last_used_block number;
%F8w}V(~4@Q0 12  beginITPUB个人空间5`:\;V ?9]f fE]h
 13     dbms_space.unused_space(upper(p_owner), upper(p_object_name), upper(p_object_type), v_total_blocks,
%wdc feI6`/c&Y HSXz0 14             v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,ITPUB个人空间;_t*_;?M|1b+~
 15             v_last_used_extent_block_id, v_last_used_block, upper(p_partition_name));ITPUB个人空间8V1\'j/Ux/a\hmDQ S
 16     dbms_output.put_line('total_blocks is ' || v_total_blocks);
D!bJm,d0 17     dbms_output.put_line('total_bytes is ' || v_total_bytes);ITPUB个人空间~0TO[d"@f
 18     dbms_output.put_line('unused_blocks is ' || v_unused_blocks);
4MuXjn$H zvlb0 19     dbms_output.put_line('unused_bytes is ' || v_unused_bytes);ITPUB个人空间 @"Yx+bdnkj
 20     dbms_output.put_line('last_used_extent_file_id is ' || v_last_used_extent_file_id);ITPUB个人空间4YUC"f_9E} ygcI
 21     dbms_output.put_line('last_used_extent_block_id is ' || v_last_used_extent_block_id);
k&{?0oQ0Yz|D9f0 22     dbms_output.put_line('last_used_block is ' || v_last_used_block);
%J!T1E^"P]0 23  end;ITPUB个人空间-rc(Lg(H;bO@
 24  /

过程已创建。

SQL> EXEC P_UNUSED_SPACE('ORD_ORDER_RECEIVE', 'TABLE', 'ZHEJIANG')ITPUB个人空间
m \dzR6d
total_blocks is 1397504
P1mMR|/y\0total_bytes is 22896705536ITPUB个人空间'yd?$]1o$X aU
unused_blocks is 0
gXs9[8Q0unused_bytes is 0ITPUB个人空间i4G`+]
c*fCT;JN
last_used_extent_file_id is 31
|[jF1T-m_cS I0last_used_extent_block_id is 110981ITPUB个人空间d+y'L4Jy^H1k_
last_used_block is 128

PL/SQL过程已成功完成。

SQL> create or replace procedure p_space_usage (p_segment_name in varchar2,
kqlx];n(T|!\0  2      p_segment_type in varchar2 default 'TABLE',
AQd#Z1nc6P0  3     p_segment_owner in varchar2 default user,
R4_6L2KI6K0  4     p_partition_name in varchar2 default '') asITPUB个人空间yjv`S
  5     v_unformatted_blocks number;
FUUTj%Q#\6uX0  6     v_unformatted_bytes number;ITPUB个人空间3TEa"_,U~U0U+e
  7     v_fs1_blocks number;
h!^kU#NIv _~0  8     v_fs1_bytes number;
3a8^UBJY8]4zM~
H,e0  9     v_fs2_blocks number;ITPUB个人空间b WH,c)z[T.i K
 10     v_fs2_bytes number;ITPUB个人空间LYz!j r+["@F(_4B
 11     v_fs3_blocks number;ITPUB个人空间?4Jq|5{l5T'r
 12     v_fs3_bytes number;
GwR)u;k;e0 13     v_fs4_blocks number;ITPUB个人空间J!KI4?'[
~ Q
 14     v_fs4_bytes number;
"O7H]6h+w"KVd0 15     v_full_blocks number;ITPUB个人空间Xfi#S;G:{a
 16     v_full_bytes number;
-~0dY6Xr#lz0 17  begin
O;U%Z9b}tT N&bH+Nr0 18     dbms_space.space_usage(upper(p_segment_owner), upper(p_segment_name), upper(p_segment_type), v_unformatted_blocks,
{~U1Lj0 19             v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
ru2yH~j0 20             v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, upper(p_partition_name));
R2xas.uI|0 21    ITPUB个人空间#Asb1la"[i
 22     dbms_output.put_line('unformatted_blocks is ' || v_unformatted_blocks);
}L1?9Gu]0 23     dbms_output.put_line('unformatted_bytes is ' || v_unformatted_bytes);
'Q'E-jb.Yc0 24     dbms_output.put_line('fs1_blocks is ' || v_fs1_blocks);ITPUB个人空间'H3W9\Uu.}.e}
 25     dbms_output.put_line('fs1_bytes is ' || v_fs1_bytes);ITPUB个人空间5tT9Bhy Gc(r
 26     dbms_output.put_line('fs2_blocks is ' || v_fs2_blocks);ITPUB个人空间:OO{$B^
l^
 27     dbms_output.put_line('fs2_bytes is ' || v_fs2_bytes);ITPUB个人空间rB~,`
L
 28     dbms_output.put_line('fs3_blocks is ' || v_fs3_blocks);ITPUB个人空间AJt!r.l
 29     dbms_output.put_line('fs3_bytes is ' || v_fs3_bytes);
@/aD Vl0 30     dbms_output.put_line('fs4_blocks is ' || v_fs4_blocks);ITPUB个人空间S0V+moi
 31     dbms_output.put_line('fs4_bytes is ' || v_fs4_bytes);
0A:ig/VOL ^1c{0 32     dbms_output.put_line('full_blocks is ' || v_full_blocks);ITPUB个人空间0YhC
h Bx
 33     dbms_output.put_line('full_bytes is ' || v_full_bytes);
R`aJ`x[m.ZS0 34  end;
:qCU S2FJ:Lq)b0 35  /

过程已创建。

SQL> EXEC P_SPACE_USAGE('ORD_ORDER_RECEIVE', 'TABLE', 'ZHEJIANG');
U3\ ? qnm#a1m&I0unformatted_blocks is 1300462
)mh,C9n { Q(I0F#lU0unformatted_bytes is 21306769408ITPUB个人空间&ouV Ch
fs1_blocks is 37
/EO'v)m4B Fx{D7jH0fs1_bytes is 606208ITPUB个人空间 OF2E(s0W(S
fs2_blocks is 52ITPUB个人空间}_eun5T0]&K
fs2_bytes is 851968ITPUB个人空间.p!s-yQ#MqF
fs3_blocks is 22ITPUB个人空间4Z~F#iCP-L
fs3_bytes is 360448ITPUB个人空间&x-l+AT)|oa3|
fs4_blocks is 903ITPUB个人空间 jM#DX"_ {0g8d
fs4_bytes is 14794752ITPUB个人空间u(T7x@| ]4IN
full_blocks is 94412
D J\H nO5|0full_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来临时解决这个问题。