Over the last year our production Oracle database grew from 1.2TB to 2TB. I don't have anything in place to track the growth over time.
So in order to track exactly what is segments are increasing the fastest I had our sys admin bring back the backup tapes from June 30, 2018.
I can then restore that backup to a dev server, create a db link and running a query across both databases and see what segments grew the most.
I will create the following view on both databases, then write a query with a full outer join looking for the biggest increase in space.
Does the query make sense? Some of the indexes come up with system generated names. But I at least have the segment name, and I can
find out more about the segment. Any suggestions would be helpful.
CREATE OR REPLACE VIEW system.segment_space_used
SELECT seg.segment_type, seg.owner, COALESCE( lob.table_name, seg.segment_name ) segment_name, lob.column_name column_name,
TO_CHAR( SUM(bytes)/1024/1024, '999,999,999') mb_char, SUM(bytes)/1024/1024 mb_num
FROM dba_segments seg
LEFT OUTER JOIN dba_lobs lob
ON seg.owner = lob.owner
AND seg.segment_name = lob.segment_name
GROUP BY seg.segment_type, seg.owner, lob.table_name, seg.segment_name, column_name
HAVING SUM(seg.bytes) > 10*1024*1024
ORDER BY SUM(seg.bytes);