Consolidated Oracle Report: Top Tables, LOBs, and Indexes (excluding SYS users)

-- Top 10 Tables by Size
SELECT * FROM (
  SELECT owner, segment_name AS table_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
  FROM dba_segments
  WHERE segment_type = 'TABLE'
    AND owner NOT LIKE 'SYS%'
  GROUP BY owner, segment_name
  ORDER BY size_gb DESC
) WHERE ROWNUM <= 10;

-- LOB Segments and Their Tables
SELECT * FROM (SELECT l.owner,
       l.table_name,
       l.column_name,
       l.segment_name AS lob_segment,
       ROUND(s.bytes/1024/1024/1024, 2) AS size_gb
FROM dba_lobs l
JOIN dba_segments s
  ON l.segment_name = s.segment_name
 AND l.owner = s.owner
WHERE l.owner NOT LIKE 'SYS%'
  AND s.segment_type = 'LOBSEGMENT'
ORDER BY size_gb DESC) where ROWNUM <= 10;

-- Top 10 Indexes by Size
SELECT * FROM (
  SELECT owner, segment_name AS index_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
  FROM dba_segments
  WHERE segment_type = 'INDEX'
    AND owner NOT LIKE 'SYS%'
  GROUP BY owner, segment_name
  ORDER BY size_gb DESC
) WHERE ROWNUM <= 10;









Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment