Oracle - Tablespace Growth

Tablespace growth perday over last 30 days

This script display per day growth of each tablespace in database for last 30 days.

Script joins 4 views dba_hist_tbspc_space_usage, dba_hist_tablespace_stat, dba_hist_snapshot and dba_tablespaces.

You can customize this script to see either data growth for yesterday or growth over last week, or for capacity planning.

Tablespace Name : The name of the tablespace

Current Size (MB) : This is the size of the tablespace in megabytes when the report was run.

Growth Per Day(MB) : This is average growth of tablespace for last 30 days.

-- Tablespace growth perday over last 30 days

-- Note: Query will run only if diagnostic pack license is available

-- This query is a basic query and can be further tunned to improve performance

SELECT part.tsname tablespace_name,

       Max(part.used_size) "Current Size (MB)",  /* Current size of tablespace */

       Round(Avg(inc_used_size), 2) "Growth Per Day(MB)" /* Growth of tablespace per day */

 FROM 

 (SELECT sub.days,

         sub.tsname,

         used_size,

         used_size - Lag (used_size, 1)

          over (PARTITION BY sub.tsname ORDER BY sub.tsname, sub.days) inc_used_size /* getting delta increase using analytic function */

       FROM  

       (SELECT TO_CHAR(hsp.begin_interval_time,'MM-DD-YYYY') days,

        hs.tsname,

        MAX((hu.tablespace_usedsize* dt.block_size )/(1024*1024)) used_size

      from

        dba_hist_tbspc_space_usage hu, /* historical tablespace usage statistics */

        dba_hist_tablespace_stat hs , /* tablespace information from the control file */

        dba_hist_snapshot hsp, /* information about the snapshots in the Workload Repository */

        dba_tablespaces dt

      where

        hu.snap_id = hsp.snap_id

        and hu.TABLESPACE_ID = hs.ts#

        and hs.tsname = dt.tablespace_name

        AND hsp.begin_interval_time > SYSDATE - 30 /* gathering info about last 30 days */

      GROUP  BY To_char(hsp.begin_interval_time, 'MM-DD-YYYY'),

        hs.tsname

      order by  hs.tsname,days) sub) part

GROUP  BY part.tsname

ORDER  BY part.tsname; 

[oracle@testing scripts]$ . oraenv

ORACLE_SID = [LONDON] ?

The Oracle base remains unchanged with value /u03/app/oracle

[oracle@testing scripts]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 13 01:34:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT part.tsname tablespace_name,

     Max(part.used_size) "Current Size (MB)", /* Current size of tablespace */      

     Round(Avg(inc_used_size), 2) "Growth Per Day(MB)" 

     /* Growth of tablespace per day */

FROM

  (SELECT sub.days,

          sub.tsname,

          used_size,

          used_size - Lag (used_size, 1) over (PARTITION BY sub.tsname

                           ORDER BY sub.tsname, sub.days) inc_used_size 

           /* getting delta increase using analytic function */

   FROM

     (SELECT TO_CHAR(hsp.begin_interval_time,'MM-DD-YYYY') days,

             hs.tsname,

             MAX((hu.tablespace_usedsize* dt.block_size)/(1024*1024)) used_size

      FROM dba_hist_tbspc_space_usage hu, 

          /* historical tablespace usage statistics */ 

          dba_hist_tablespace_stat hs , 

          /* tablespace information from the control file */ 

          dba_hist_snapshot hsp, 

          /* information about the snapshots in the Workload Repository */ 

          dba_tablespaces dt

      WHERE hu.snap_id = hsp.snap_id

        AND hu.TABLESPACE_ID = hs.ts#

        AND hs.tsname = dt.tablespace_name

        AND hsp.begin_interval_time > SYSDATE - 30 

            /* gathering info about last 30 days */

      GROUP BY To_char(hsp.begin_interval_time, 'MM-DD-YYYY'),

               hs.tsname

      ORDER BY hs.tsname,

               days) sub) part

GROUP BY part.tsname

ORDER BY part.tsname;

 

TABLESPACE_NAME      Current Size (MB) Growth Per Day(MB)

-------------------- ----------------  ------------------

EXAMPLE                        279608              69.07

SYSAUX                      6124.1875              35.53

SYSTEM                       940.6875                .04

UNDOTBS1                    23413.313              24.13

USERS                       1656.5625               2.13