Skip to Main Content

Help with a query to show database growth

Brian.BJun 25 2019 — edited Jun 26 2019

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.

Thanks

CREATE OR REPLACE VIEW system.segment_space_used

AS

   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);

Comments
Post Details
Added on Jun 25 2019
2 comments
2,226 views