Find Top 10 Space used Segments Including LOB in Oracle DB

Find Top 10 Space used Segments Including LOB  in Oracle DB

select s.owner || '.' ||  l.table_name table_name,
       l.column_name,
       s.SEGMENT_TYPE,
       s.segment_NAME,
       s.PARTITION_NAME,
       s.TABLESPACE_NAME,
       count(1) segment_cnt,
       trunc(sum(bytes)/1024/1024) size_MB,
       l.SECUREFILE
from dba_segments s,
     dba_lobs l
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
group by  s.owner || '.' ||  l.table_name, l.column_name,
       s.SEGMENT_TYPE,
       s.segment_NAME, s.partition_name,
       s.TABLESPACE_NAME, l.SECUREFILE
ORDER BY  size_MB desc fetch first 10 rows only ;

or

select * from
(
select s.owner || '.' ||  l.table_name table_name,
       l.column_name,
       s.SEGMENT_TYPE,
       s.segment_NAME,
       s.PARTITION_NAME,
       s.TABLESPACE_NAME,
       count(1) segment_cnt,
       trunc(sum(bytes)/1024/1024) size_MB,
       l.SECUREFILE
from dba_segments s,
     dba_lobs l
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
group by  s.owner || '.' ||  l.table_name, l.column_name,
       s.SEGMENT_TYPE,
       s.segment_NAME, s.partition_name,
       s.TABLESPACE_NAME, l.SECUREFILE
ORDER BY  size_MB desc
)
where rownum < 11
/