Oracle DBA FAQ

Would you like to react to this message? Create an account in a few clicks or log in to continue.
Oracle DBA FAQ

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


    Script to find tablespace growth

    avatar
    Admin
    Admin


    Posts : 17
    Join date : 2010-02-08

    Script to find tablespace growth Empty Script to find tablespace growth

    Post  Admin 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 Sun May 19, 2024 12:13 am