col owner for a10
col table_name for a30
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
FROM ( SELECT A.OWNER OWNER,
A.SEGMENT_NAME,
A.SEGMENT_TYPE,
B.LAST_ANALYZED,
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,
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)
WHERE GREATEST (ROUND ( 100 * ( NVL (HWM - AVG_USED_BLOCKS, 0 ) /
GREATEST (NVL (HWM, 1), 1 )),
2 ),
0 ) > 50
AND OWNER like 'DEVELOPER'
AND BLOCKS > 100
order by WASTE_PER desc ;
oracle查看表的高水位
原创
©著作权归作者所有:来自51CTO博客作者wx63159e54cdd9a的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:oracle11G手动建库
下一篇:oracle行转列
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Oracle查看高水位脚本
其中SYSTEM为需要查找的表空间名称,需要替换为实际的表空间名称。
python oracle linux unity sql