with t as (select seg.segment_name , bytes/1024/1024 as sgesize from dba_segments seg order by sgesize desc ),
t1 as (
select rownum as rn , t.* from t )
select * from t1 where t1.rn <10;
如果查询的segment 是clob等类型，需进一步确定其表名及字段名
SELECT B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,
a.SEGMENT_TYPE, ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) GB
FROM DBA_SEGMENTS A LEFT JOIN DBA_LOBS B ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
WHERE B.SEGMENT_NAME = 'SYS_LOB00000********17$$'
GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE;
