包括clob segment 大字段 表的大小统计
SELECT OWNER,TABLE_NAME,SUM(daxiao) from (
SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as daxiao FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM'))
GROUP BY SEGMENT_NAME,owner
union ALL
select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as daxiao from dba_lobs lob,dba_segments seg
where lob.segment_name=seg.segment_name and lob.OWNER NOT IN ('SYS','SYSTEM') group by lob.owner,lob.table_name
)
GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC;
SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as daxiao FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM'))
GROUP BY SEGMENT_NAME,owner
union ALL
select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as daxiao from dba_lobs lob,dba_segments seg
where lob.segment_name=seg.segment_name and lob.OWNER NOT IN ('SYS','SYSTEM') group by lob.owner,lob.table_name
)
GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23944170/viewspace-2151923/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
