查出碎片最多的表
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_OWNERand SEGMENT_NAME = B.TABLE_NAMEand SEGMENT_TYPE = 'TABLE PARTITION'AND B.TABLESPACE_NAME = C.NAMEAND D.OWNER = B.TABLE_OWNERAND D.TABLE_NAME = B.TABLE_NAMEAND 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) > 25AND BLOCKS > 128 AND OWNER in (user_name1,user_name2) ORDER BY 1 ASC, 4 DESC;
