关注waste_per越大说明高水位越高
SELECT w.owner, w.table_name, tc.comments, hwm, avg_used_blocks,
greatest(round(100 * (nvl(hwm - avg_used_blocks, 0) /
greatest(nvl(hwm, 1), 1)), 2), 0) waste_per,
w.last_analyzed, w.num_rows
FROM (SELECT a.owner, a.blocks - b.empty_blocks - 1 hwm, table_name,
decode(round((b.avg_row_len * num_rows *
(1 + (pct_free / 100))) / 8192, 0), 0, 1,
round((b.avg_row_len * num_rows *
(1 + (pct_free / 100))) / 8192, 0)) + 2 avg_used_blocks,
b.last_analyzed last_analyzed, b.num_rows
FROM dba_segments a, dba_tables b
WHERE a.owner = b.owner
AND segment_name = table_name
AND segment_type = 'TABLE' /*对象类型*/
) w
JOIN dba_tab_comments tc
ON tc.table_name = w.table_name
AND tc.owner = w.owner
--and table_name='' --过滤的表
ORDER BY waste_per DESC;
复制
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。