前阶段遇到的一个问题，一个Index在几分钟内忽然增长了6G左右，且增长的
这个block都是未格式化的块，详细情况见：
http://www.itpub.net/thread-1349570-1-3.html

SQL> show serveroutput
serveroutput OFF
SQL>
SQL> set serveroutput on;
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL>
declare
    l_fs1_bytes number;
    l_fs2_bytes number;
    l_fs3_bytes number;
    l_fs4_bytes number;
    l_fs1_blocks number;
    l_fs2_blocks number;
    l_fs3_blocks number;
    l_fs4_blocks number;
    l_full_bytes number;
    l_full_blocks number;
    l_unformatted_bytes number;
    l_unformatted_blocks number;
begin
      dbms_space.space_usage('DFMS',
         'IDX1_WIP_D_WO_DETAIL_COMID',
         'INDEX',
         fs1_bytes=> l_fs1_bytes,
         fs1_blocks         => l_fs1_blocks,
         fs2_bytes          => l_fs2_bytes,
         fs2_blocks         => l_fs2_blocks,
         fs3_bytes          => l_fs3_bytes,
         fs3_blocks         => l_fs3_blocks,
         fs4_bytes          => l_fs4_bytes,
         fs4_blocks         => l_fs4_blocks,
         full_bytes         => l_full_bytes,
         full_blocks        => l_full_blocks,
         unformatted_blocks => l_unformatted_blocks,
         unformatted_bytes => l_unformatted_bytes   );
      dbms_output.put_line(' FS1 Blocks (0~25%) = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
      dbms_output.put_line(' FS2 Blocks(25~50%) = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
      dbms_output.put_line(' FS3 Blocks(50~75%) = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
      dbms_output.put_line(' FS4 Blocks (75~100%)= '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
      dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
dbms_output.put_line(' Unformatted Blocks = '||l_unformatted_blocks||' and Bytes = '||l_unformatted_bytes);
   end;
   /

FS1 Blocks(0~25%) = 0 and Bytes = 0
FS2 Blocks(25~50%) = 1548 and Bytes = 25362432
FS3 Blocks(50~75%) = 0 and Bytes = 0
FS4 Blocks(75~100%) = 0 and Bytes = 0
Full Blocks = 3405 and Bytes = 55787520
Unformatted Blocks = 1137328 and Bytes = 18633981952

目前为止，还没有找到如何解决，为了及时发现类似bug导致的异常，我们需要定期查看一些table, index，LOB等是否有异常增长的现象。我们可以设置如下SQL,
通过某个监控系统或schedule job定期查询，一旦发现异常，及时回馈并处理。

select a.owner, a.segment_name, b.num_rows, a.bytes/1024/1024/1024||' G'
from   dba_segments a, dba_tables b
where a.owner='DFMS' and a.segment_name=b.table_name
      and a.segment_type in ('TABLE') and bytes>=8*1024*1024*1024
      order by bytes desc ;

select a.owner, a.segment_name,b.table_name, b.num_rows,
        a.bytes/1024/1024/1024||' G (Index Size)'
from   dba_segments a, dba_indexes b
where a.owner='DFMS' and a.segment_name=b.index_name
      and a.segment_type in ('INDEX') and a.bytes>=6*1024*1024*1024
      order by a.bytes desc ;

select a.owner, a.segment_name,b.table_name, a.bytes/1024/1024/1024||' G (LOB Size)'
from   dba_segments a, dba_lobs b
where a.owner='DFMS' and a.segment_name=b.segment_name
      and a.segment_type in ('LOBSEGMENT') and a.bytes>=5*1024*1024*1024
      order by a.bytes desc ;

相关的视图：
select * from dba_tables ;
select * from dba_indexes ;
select * from dba_lobs ;
select * from dba_segments ;

SQL查询table或index异常增长问题 - space_usage (show_space)