oracle碎片小结(2)

一种笨的方式就是ctas一个表和原来的做下比较，看看空间大小的变化，还有一种就方法就是看视图里数据（要及时analyze才准确）
查看user_tables和user_segments

eg:

SQL> select num_rows,blocks,empty_blocks,avg_space,avg_row_len,last_analyzed from user_tables where table_name='TEST1';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ------------ ---------- ----------- -------------
    393216        562           78         23          13 2010-5-27 21:

AVG_SPACE:表示block内的空闲空间大小，根据pctfree参数来参考，这里以默认为准，在block=8192里，允许空间的空间是8192 × =819.2，也就是说在block剩余空间是819.2时，oracle就认为它是满块了，如果再有数据就要寻找新的block了，当表的AVG_SPACE远远大于819.2时，就说明有碎片了，因为块没有被充分利用，产生block-level碎片。可以通过alter table move来整理。

那可以节省多少空间呢？
        
   数据的实际大小=AVG_ROW_LEN × NUM_ROWS=13 × 393216=5111808
          表的实际大小  =BLOCKS × block_size   =562 * 8192 =4603904
                                                    
        

理论上AVG_ROW_LEN × NUM_ROWS <= BLOCKS × block_size 如果相差比较大，那就需要整理，也可以粗略的算下可以节省多少空间，可现在实际上确
AVG_ROW_LEN × NUM_ROWS > BLOCKS × block_size ,不知道的是什么原因？

突然想到我这个test1表里最后一个字段是clob类型的，而clob在数据大于4k时（一个lob字段包括lobindex和lobsegment,），是存储在另外的log segment中的。

而clob类型的d字段没有存储任何数据，查询user_lobs视图

SQL> select ul.table_name,ul.segment_name, ul.chunk,se.blocks,se.bytes from user_lobs ul ,user_segments se
  2  where ul.segment_name=se.segment_name
  3  and ul.table_name='TEST1'
  4  ;

TABLE_NAME                     SEGMENT_NAME                        CHUNK     BLOCKS      BYTES
------------------------------ ------------------------------ ---------- ---------- ----------
TEST1                          SYS_LOB0000183465C00004$$            8192          8      65536

可这个65536和那两个差值还差很多，再说这8个block还是empty，搞不懂了，谁知道请指点，先谢谢了啊

换个角度会什么样的？我又用CTAS穿件表test4，这回就符合理论值，无论用

alter table table_name move,还是alter table table_name shrink space compact，都是符合理论值的

create table test4 as select * from test1

分析后： analyze  table test4 compute statistics

SQL> select num_rows,blocks,empty_blocks,avg_space,avg_row_len,last_analyzed from user_tables where table_name='TEST4';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ------------ ---------- ----------- -------------
    393216        802            6        820          12 2010-5-28 12:

   数据的实际大小=AVG_ROW_LEN × NUM_ROWS=13 × 393216=5111808
          表的实际大小  =BLOCKS × block_size   =562 * 8192 =6569984
                                                    

index碎片查找

识别索引是否有碎片

获得关于索引的信息，用下面的命令

analyze index index_name validate structure 或validate index index_name