Description:
This query used to find and calculate the top 20 tables by growth for users requested days.
Script:
select * from (select
so.owner,
so.object_name,
–so.subobject_name,
so.object_type,
so.tablespace_name,
round(sum(ss.space_used_delta)/1024/1024) growth_mb
from
dba_hist_seg_stat ss,
dba_hist_seg_stat_obj so
where
ss.obj# = so.obj#
and ss.dataobj# = so.dataobj#
and so.owner != ‘** MISSING **’
and so.object_name not like ‘BIN$%’ –
and so.object_type =’TABLE’
and ss.snap_id > (
select min(sn.snap_id)
from dba_hist_snapshot sn
where
sn.dbid = (select dbid from v$database)
and sn.END_INTERVAL_TIME > trunc(sysdate) – &days
)
group by
so.owner,
so.object_name,
–so.subobject_name,
so.object_type,
so.tablespace_name
order by 5 desc)
where ROWNUM <= 20;
