Our in-house database is growing fast. I am trying to pin down which table is growing the most counting indexes and lobs.
To do that I have been working on this query. I want to make sure that I am not double counting the lobs, or indexes.
Does this query make sense? Do you see any errors in my logic?
Thanks
SELECT tab.owner, tab.table_name, lob.column_name,
TO_CHAR( SUM( NVL(seg_tab.bytes, 0)+NVL(seg_lob.bytes, 0)+NVL(seg_idx.bytes, 0) ) /1024/1024, '999,999,999' ) AS sum_lob_mb
FROM dba_tables tab
LEFT OUTER JOIN dba_lobs lob
ON tab.owner = lob.owner
AND tab.table_name = lob.table_name
LEFT OUTER JOIN dba_indexes idx
ON tab.owner = idx.owner
AND tab.table_name = idx.table_name
AND idx.index_type NOT IN ( 'LOB', 'IOT - TOP', 'CLUSTER' )
LEFT OUTER JOIN dba_segments seg_tab
ON lob.owner = seg_tab.owner
AND lob.table_name = seg_tab.segment_name
LEFT OUTER JOIN dba_segments seg_lob
ON lob.owner = seg_lob.owner
AND lob.segment_name = seg_lob.segment_name
LEFT OUTER JOIN dba_segments seg_idx
ON lob.owner = seg_idx.owner
AND idx.index_name = seg_idx.segment_name
GROUP BY tab.owner, tab.table_name, lob.column_name
HAVING SUM( NVL(seg_tab.bytes, 0)+NVL(seg_lob.bytes, 0)+NVL(seg_idx.bytes, 0) ) /1024/1024 > 1024
ORDER BY SUM( NVL(seg_tab.bytes, 0)+NVL(seg_lob.bytes, 0)+NVL(seg_idx.bytes, 0) );