查出碎片最多的表

先搞一个简单的

select t.table_name, 
       avg_row_len * num_rows / (1 - pct_free / 100) / 8192 actual_block, 
       alloc_block, 
       1 - ((avg_row_len * num_rows / (1 - pct_free / 100) / 8192) / alloc_block) percen, 
       'alter table '||t.table_name||'enable row movement'||';' enable_row, 
       'alter table '||t.table_name||'shrink space'||';' ddl_shrink 
  from (select segment_name, sum(blocks) alloc_block 
          from user_extents 
        having sum(blocks) > 8 
         group by segment_name) a, 
       user_tables t 
 where a.segment_name = t.table_name 
   and num_rows is not null 
   and num_rows > 0 
 order by 4 desc;

在上一个详细的

 SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE TYPE,                                          
      GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) "WASTE_PER(%)",
      ROUND(BYTES/1024/1024, 2) "Total(M)", NUM_ROWS,                                                      
      BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,                                             
      DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,                
      NEXT_EXTENT, MAX_FREE_SPACE,                                                                  
      O_TABLESPACE_NAME TABLESPACE_NAME                                                             
FROM                                                                                          
      (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,                               
      B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,                                     
      A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,                                                            
      DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),0, 1,                                                                                         
      ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,                                                                        
      ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,              
      ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,                  
      A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME     
    FROM SYS.DBA_SEGMENTS A,SYS.DBA_TABLES B,SYS.TS$ C                                                                                     
    WHERE A.OWNER =B.OWNER
    and SEGMENT_NAME = TABLE_NAME
    and SEGMENT_TYPE = 'TABLE'
    AND B.TABLESPACE_NAME = C.NAME                                                                    
UNION ALL                                                                                     
SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,           
       B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,                                     
       A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,                                                            
       DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),0, 1,                                                                                         
       ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)                   
          ) + 2 AVG_USED_BLOCKS,                                                                        
       ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,               
       ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,                 
       A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,                                                     
       B.TABLESPACE_NAME O_TABLESPACE_NAME                                                           
FROM SYS.DBA_SEGMENTS A,SYS.DBA_TAB_PARTITIONS B,SYS.TS$ C,SYS.DBA_TABLES D                                                                              
WHERE A.OWNER = B.TABLE_OWNER
and SEGMENT_NAME = B.TABLE_NAME
and SEGMENT_TYPE = 'TABLE PARTITION'
AND B.TABLESPACE_NAME = C.NAME
AND D.OWNER = B.TABLE_OWNER
AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME),                                                         
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES) MAX_FREE_SPACE                                                                                
FROM SYS.DBA_FREE_SPACE                                                                       
GROUP BY TABLESPACE_NAME)                                                                     
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND                                               
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND BLOCKS > 128  
AND OWNER in (user_name1,user_name2)                                                             
ORDER BY 1 ASC, 4 DESC;