DATABASE GROWTH / Disk Used by Object Type

DATABASE GROWTH / Disk Used by Object Type

define schema_name='SYS'
define days_back=10

select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
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 end_interval_time > trunc(sysdate) - &days_back
and 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.owner = '&schema_name'
and space_used_delta > 0;




Space used (M) Total Schema Size (M) Percent of Total Disk Usage
-------------- --------------------- -----------------------------------------
    19626.4335            1564335.63 1.25%




DATABASE GROWTH delta


define schema_name='SYS'
define days_back=10


ttitle "Total Disk Used by Object Type"
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes)/1024/1024 "Total Space (M)",
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 end_interval_time > trunc(sysdate) - &days_back
and 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 space_used_delta > 0
and c.owner = '&schema_name'
group by rollup(segment_type);



SEGMENT_TYPE       Space used (M) Total Space (M) Percent of Total Disk Usage
------------------ -------------- --------------- -----------------------------------------
INDEX                  421.957651      357853.375 .12%
INDEX PARTITION        1297.58915           33745 3.85%
TABLE                  119.543896       434165.25 .03%
TABLE PARTITION        17787.3428          738572 2.41%
                       19626.4335      1564335.63 1.25%
SQL>



Object Growth 

set linesize 300 pagesize 300

col OWNER for a20
col OBJECT_NAME for a35
with 
 awr_seg_stat as (
   select
      ts#,obj#,dataobj# 
     ,snap_id
     ,space_used_total
     ,space_used_delta
     ,space_allocated_total
     ,space_allocated_delta
     ,physical_writes_delta
     ,physical_write_requests_delta
   from dba_hist_seg_stat s
   --where obj# in (select object_id from dba_objects where owner='')
)
,top_segs_by_growth as (
   select *
   from (
      select
         ts#,obj#
        ,sum(space_allocated_delta) delta
        ,dense_rank()over(order by sum(space_allocated_delta) desc) N
      from awr_seg_stat
      group by ts#,obj#
   )
   where N<=10 -- topN
)
select
   s.n
  ,o.owner,o.object_type,o.object_name
  ,round(s.delta/1024/1024) "Delta(MB)"
from top_segs_by_growth s    ,dba_objects o
where s.obj# = o.object_id
order by 1
/

         N OWNER                OBJECT_TYPE             OBJECT_NAME                          Delta(MB)
---------- -------------------- ----------------------- ----------------------------------- ----------
         6 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         37
         6 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         37
         6 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         37
         6 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         37
         7 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         37
         7 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         37
         8 SYS                  TABLE PARTITION         WRH$_LATCH_CHILDREN                         36
         9 SYS                  TABLE PARTITION         WRH$_ACTIVE_SESSION_HISTORY                 31
        10 SYS                  TABLE PARTITION         WRH$_ACTIVE_SESSION_HISTORY                 30
SQL>