Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    Script to find tablespace growth

    Share

    Admin
    Admin

    Posts : 17
    Join date : 2010-02-08

    Script to find tablespace growth

    Post  Admin on Tue Feb 12, 2013 1:27 pm

    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;

      Current date/time is Thu Sep 21, 2017 11:36 am