Query of Predict Oracle database growth

Biasanya dibuthkan untuk kepentingan capacity planning


for all databas size :
/* Formatted on 2009/12/02 11:12 (Formatter Plus v4.8.8) */
SELECT TIME, sum(tblsize_in_mb) dbsize, sum(usedsize_in_mb) db_used_size, sum(tblsize_in_mb) - sum(usedsize_in_mb) dbsize_free
  FROM (SELECT   /*+ parallel(c, 5) */
                 TO_CHAR (c.begin_interval_time, 'MON') "TIME", b.NAME,
                 ROUND (MAX (a.tablespace_size * d.block_size) / 1024 / 1024
                       ) tblsize_in_mb,
                 ROUND (MAX (a.tablespace_maxsize * d.block_size) / 1024
                        / 1024
                       ) totalsize_in_mb,
                 ROUND (MAX (a.tablespace_usedsize * d.block_size) / 1024
                        / 1024
                       ) usedsize_in_mb
            FROM dba_hist_tbspc_space_usage a,
                 v$tablespace b,
                 dba_hist_snapshot c,
                 dba_tablespaces d
           WHERE a.tablespace_id = b.ts#
             AND a.snap_id = c.snap_id
             AND b.NAME = d.tablespace_name
        GROUP BY TO_CHAR (c.begin_interval_time, 'MON'),
                 b.NAME
        ORDER BY 3, 2, 1 DESC)
group by time
order by time desc

melihat dari pertumbuhan segement :


select *
from (select to_char(end_interval_time, 'MM/DD/YY') mydate,
     sum(space_used_delta) / 1024 / 1024 "Space used (MB)",
     avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
   dba_hist_snapshot sn,
   dba_hist_seg_stat a,
   dba_objects b,
   dba_segments c
--where begin_interval_time > trunc(sysdate) - &days_back
where sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
--and c.segment_name = '&segment_name'
group by to_char(end_interval_time, 'MM/DD/YY'))
order by to_date(mydate, 'MM/DD/YY');