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;
Post a Comment
Post a Comment