SQL to find Tablespace that has grown 10% in last 24 hours
WITH ts_info as (
select ts#, tsname, max(block_size) block_size
from dba_hist_datafile
group by ts#, tsname),
-- Get the max and min snaphsot id for last 24 hrs from dba_hist_snapshot
snap_info as (
select max(trunc(end_interval_time)) dd, max(s.snap_id) snap_id, min(trunc(end_interval_time)) ddmin, min(s.snap_id) snap_idmin
from dba_hist_snapshot s where end_interval_time > sysdate -1
)
-- Calculate growth of tablspace size in 24 hours
select f.tsname tsname, round((sp.tablespace_size-spmin.tablespace_size)*100/spmin.tablespace_size) growth
from dba_hist_tbspc_space_usage sp, dba_hist_tbspc_space_usage spmin,
ts_info f,
snap_info s
where s.snap_id = sp.snap_id
and SP.TABLESPACE_ID=f.ts#
and s.snap_idmin = spmin.snap_id
and SPMIN.TABLESPACE_ID=f.ts# and (sp.tablespace_size-spmin.tablespace_size)*100/spmin.tablespace_size > 10
order by f.tsname;
WITH ts_info as (
select ts#, tsname, max(block_size) block_size
from dba_hist_datafile
group by ts#, tsname),
-- Get the max and min snaphsot id for last 24 hrs from dba_hist_snapshot
snap_info as (
select max(trunc(end_interval_time)) dd, max(s.snap_id) snap_id, min(trunc(end_interval_time)) ddmin, min(s.snap_id) snap_idmin
from dba_hist_snapshot s where end_interval_time > sysdate -1
)
-- Calculate growth of tablspace size in 24 hours
select f.tsname tsname, round((sp.tablespace_size-spmin.tablespace_size)*100/spmin.tablespace_size) growth
from dba_hist_tbspc_space_usage sp, dba_hist_tbspc_space_usage spmin,
ts_info f,
snap_info s
where s.snap_id = sp.snap_id
and SP.TABLESPACE_ID=f.ts#
and s.snap_idmin = spmin.snap_id
and SPMIN.TABLESPACE_ID=f.ts# and (sp.tablespace_size-spmin.tablespace_size)*100/spmin.tablespace_size > 10
order by f.tsname;