实例讲解Oracle表碎片的具体起因及解决办法(3)

作者: 李丝 出处:　

查询出HWM以下的数据块的个数(可能由于delete, 数据块中并不包含数据)：

This will update the table statistics. After generating the 
statistics, to determine the high water mark: 
SELECT blocks, empty_blocks, num_rows 
FROM user_tables 
WHERE table_name = ;

下面给出一个综合的sql语句，它可以查询出浪费空间的表(浪费超过25%)，而且还计算出其它信息(使用时根据具体情况修改where子句中的blocks，owner限制条件)：

SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, 
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB, 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 OWNER = '??' AND BLOCKS > 128
ORDER BY 10 DESC, 1 ASC, 2 ASC;

各列说明：

WASTE_PER：已分配空间中水线以下的空闲空间(即浪费空间)的百分比。

TABLE_KB：该表目前已经分配的所有空间的大小，以k为单位。

NUM_ROWS：在在表中数据的行数。

BLOCKS：该表目前已经分配的数据块的块数，包含水线以上的部分。

EMPTY_BLOCKS：已分配空间中水线以上的空闲空间。

HIGHWATER_MARK：目前的水线。

AVG_USED_BLOCKS：理想情况下(没有行迁移)，该表数据应该占用的数据块的个数。

CHAIN_PER：发生行迁移现象的行占总行的比率。

EXTENTS：该表目前已经分配的extent数。

MAX_EXTENTS：该表可以分配的最大extent的个数。

ALLO_EXTENT_PER：目前已分配的extent的个数占可以分配最大extent的比率。

CAN_EXTEND_SPACE：是否可以分配下一个extent。

NEXT_EXTENT：下一个extent的大小。

MAX_FREE_SPACE：表的已分配空间中最大的空闲空间。