Check the top larger size LOB objects in Oracle

Find the larger size lob consuming space in Oracle except for user sys or system lob. You can use owner = ‘schemaname’ to check for the specific owner/user.

SET LINESIZE 200
SET PAGESIZE 0
COLUMN owner FORMAT A10
COLUMN table_name FORMAT A25
COLUMN column_name FORMAT A25
COLUMN segment_name FORMAT A25
COLUMN size_mb FORMAT 99999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name where l.owner not in ('SYS','SYSTEM')
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 10;