分类： Oracle
2008-01-29 11:20:12
今天突然发现一个表空间的空间不足，后台开始报错：ORA-1653。
奇怪的是，上周才检查过表空间的容量，这个表空间还有20G左右的空间，怎么这么快就用完了。
检查了是哪个表占用了大量的空间：
SQL> SELECT * FROM
2 (
3 SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 G
4 FROM DBA_SEGMENTS
5 WHERE OWNER = 'ZHEJIANG'
6 GROUP BY SEGMENT_NAME
7 ORDER BY 2 DESC
8 )
9 WHERE ROWNUM < 5;
SEGMENT_NAME G
-------------------------------- ----------
ORD_ORDER_RECEIVE 21.3242188
ORD_HIT_COMM 5.47070313
CAT_LOG_AREA_PRICE 5.0625
ORD_ORDER_ITEM 3.19335938
问题对象已经找到，这个表的空间占用了21g，而正常情况下，这个表应该不会超过3个G。
检查表的数据量是否发生数据量级的增长：
SQL> SELECT COUNT(*) FROM ZHEJIANG.ORD_ORDER_RECEIVE;
COUNT(*)
----------
4141294
总共四百万条记录，这个数据量也很正常，再看看表的记录长度：
SQL> SELECT AVG_ROW_LEN FROM DBA_TABLES
2 WHERE TABLE_NAME = 'ORD_ORDER_RECEIVE'
3 AND OWNER = 'ZHEJIANG';
AVG_ROW_LEN
-----------
323
这个平均长度也没有问题，如果根据表的大小除以表记录数计算来计算平均长度：
SQL> select 21.3242188*1024*1024*1024/4136937 from dual;
21.3242188*1024*1024*1024/4136937
---------------------------------
5534.70009
这个长度就离谱了，计算这个表的最大长度：
SQL> SELECT SUM(DATA_LENGTH) FROM DBA_TAB_COLUMNS
2 WHERE OWNER = 'ZHEJIANG'
3 AND TABLE_NAME = 'ORD_ORDER_RECEIVE';
SUM(DATA_LENGTH)
----------------
777
最大长度才777，可是现在这张表的平均大小已经到了5K多，看来表中存在大量没有使用的BLOCK。
SQL> SELECT INDEX_NAME,
2 SUM(S.BYTES)/1024/1024 M
3 FROM DBA_INDEXES I, DBA_SEGMENTS S
4 WHERE I.OWNER = 'ZHEJIANG'
5 AND S.OWNER = 'ZHEJIANG'
6 AND SEGMENT_TYPE = 'INDEX'
7 AND SEGMENT_NAME = INDEX_NAME
8 AND TABLE_NAME = 'ORD_ORDER_RECEIVE'
9 GROUP BY INDEX_NAME;
INDEX_NAME M
------------------------------ ----------
TU_ORD_ORDER_COMB_ITEM_FLAG 224
TU_ORD_ORDER_RECEIVE_PROD_ID 204
IND_ORD_ORDER_REC_REC_DATE 108
PK_ORD_ORDER_RECEIVE 196
IND_ORD_RECEIVE_BUYERSENDER 340
索引的大小都很正常，看来问题只是出在了表上，下面通过两个DBMS_SPACE来检查表的block使用情况，这里对两个过程进行了简单的封装：
SQL> create or replace procedure p_unused_space(p_object_name in varchar2,
2 p_object_type in varchar2 default 'TABLE',
3 p_owner in varchar2 default user,
4 p_partition_name in varchar2 default '') is
5 v_total_blocks number;
6 v_total_bytes number;
7 v_unused_blocks number;
8 v_unused_bytes number;
9 v_last_used_extent_file_id number;
10 v_last_used_extent_block_id number;
11 v_last_used_block number;
12 begin
13 dbms_space.unused_space(upper(p_owner), upper(p_object_name), upper(p_object_type), v_total_blocks,
14 v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
15 v_last_used_extent_block_id, v_last_used_block, upper(p_partition_name));
16 dbms_output.put_line('total_blocks is ' || v_total_blocks);
17 dbms_output.put_line('total_bytes is ' || v_total_bytes);
18 dbms_output.put_line('unused_blocks is ' || v_unused_blocks);
19 dbms_output.put_line('unused_bytes is ' || v_unused_bytes);
20 dbms_output.put_line('last_used_extent_file_id is ' || v_last_used_extent_file_id);
21 dbms_output.put_line('last_used_extent_block_id is ' || v_last_used_extent_block_id);
22 dbms_output.put_line('last_used_block is ' || v_last_used_block);
23 end;
24 /
过程已创建。
SQL> EXEC P_UNUSED_SPACE('ORD_ORDER_RECEIVE', 'TABLE', 'ZHEJIANG')
total_blocks is 1397504
total_bytes is 22896705536
unused_blocks is 0
unused_bytes is 0
last_used_extent_file_id is 31
last_used_extent_block_id is 110981
last_used_block is 128
PL/SQL 过程已成功完成。
SQL> create or replace procedure p_space_usage (p_segment_name in varchar2,
2 p_segment_type in varchar2 default 'TABLE',
3 p_segment_owner in varchar2 default user,
4 p_partition_name in varchar2 default '') as
5 v_unformatted_blocks number;
6 v_unformatted_bytes number;
7 v_fs1_blocks number;
8 v_fs1_bytes number;
9 v_fs2_blocks number;
10 v_fs2_bytes number;
11 v_fs3_blocks number;
12 v_fs3_bytes number;
13 v_fs4_blocks number;
14 v_fs4_bytes number;
15 v_full_blocks number;
16 v_full_bytes number;
17 begin
18 dbms_space.space_usage(upper(p_segment_owner), upper(p_segment_name), upper(p_segment_type), v_unformatted_blocks,
19 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
20 v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, upper(p_partition_name));
21
22 dbms_output.put_line('unformatted_blocks is ' || v_unformatted_blocks);
23 dbms_output.put_line('unformatted_bytes is ' || v_unformatted_bytes);
24 dbms_output.put_line('fs1_blocks is ' || v_fs1_blocks);
25 dbms_output.put_line('fs1_bytes is ' || v_fs1_bytes);
26 dbms_output.put_line('fs2_blocks is ' || v_fs2_blocks);
27 dbms_output.put_line('fs2_bytes is ' || v_fs2_bytes);
28 dbms_output.put_line('fs3_blocks is ' || v_fs3_blocks);
29 dbms_output.put_line('fs3_bytes is ' || v_fs3_bytes);
30 dbms_output.put_line('fs4_blocks is ' || v_fs4_blocks);
31 dbms_output.put_line('fs4_bytes is ' || v_fs4_bytes);
32 dbms_output.put_line('full_blocks is ' || v_full_blocks);
33 dbms_output.put_line('full_bytes is ' || v_full_bytes);
34 end;
35 /
过程已创建。
SQL> EXEC P_SPACE_USAGE('ORD_ORDER_RECEIVE', 'TABLE', 'ZHEJIANG');
unformatted_blocks is 1300462
unformatted_bytes is 21306769408
fs1_blocks is 37
fs1_bytes is 606208
fs2_blocks is 52
fs2_bytes is 851968
fs3_blocks is 22
fs3_bytes is 360448
fs4_blocks is 903
fs4_bytes is 14794752
full_blocks is 94412
full_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来临时解决这个问题。
