统计表的大小--按大小倒序[@more@]select dense_rank() over(order by max(sizepertab) desc,segment_name asc) rank,segment_name,segment_type,tablespace_name,max(sizepertabpar)||'M',max(sizepertab)||'M'
from (SELECT t1.segment_name,t1.segment_type,t1.tablespace_name,SUM(t1.bytes/1024/1024) over(partition by t1.segment_name,t1.segment_type) sizepertab,
SUM(t1.bytes/1024/1024) over(partition by t1.segment_name,t1.segment_type,t1.tablespace_name) sizepertabpar
FROM User_Segments t1
WHERE t1.segment_type like 'TABLE%') t
group by segment_name,segment_type,tablespace_name
order by max(sizepertab) desc,max(sizepertabpar) desc,segment_name asc
from (SELECT t1.segment_name,t1.segment_type,t1.tablespace_name,SUM(t1.bytes/1024/1024) over(partition by t1.segment_name,t1.segment_type) sizepertab,
SUM(t1.bytes/1024/1024) over(partition by t1.segment_name,t1.segment_type,t1.tablespace_name) sizepertabpar
FROM User_Segments t1
WHERE t1.segment_type like 'TABLE%') t
group by segment_name,segment_type,tablespace_name
order by max(sizepertab) desc,max(sizepertabpar) desc,segment_name asc
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7691350/viewspace-1045158/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7691350/viewspace-1045158/