Tablespace Usage History Report

Useful report to show tablespace daily usage and space usage increasing ratio.

Please make sure you are licensed to run sqls against DBA_HIST_*, like  dba_hist_tbspc_space_usage .

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME                            TYPE     VALUE
------------------------------- -------- -----------------------
control_management_pack_access  string   DIAGNOSTIC+TUNING

The length of the report depends on  AWR retention. Please refer to  “Change AWR Retention & Interval” for how to change AWR retention.

select THE_DATE,
       TABLESPACE,
       SIZE_IN_GB_TD,
       SIZE_IN_GB_YTD,
       SIZE_IN_GB_TD - SIZE_IN_GB_YTD  SIZE_IN_GB_INCREASED
from ( 
     select 
            to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') THE_DATE,
            tbl.name TABLESPACE,
            round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2) SIZE_IN_GB_TD,
            lag( round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2),1) over ( order by to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') ) SIZE_IN_GB_YTD 
     from 
          dba_hist_snapshot histsnap,
          dba_hist_tbspc_space_usage tblusage,
          v$tablespace tbl,
          v$parameter pmt
    where 
          histsnap.SNAP_ID = tblusage.SNAP_ID
      and tbl.ts# = tblusage.TABLESPACE_ID
      and tbl.name ='&TABLESPACENAME'
      and pmt.name ='db_block_size'
   group by 
          to_char( histsnap.begin_interval_time, 'YYYY-MM-DD'),
          tbl.name,
          pmt.value
 order by 
         to_char( histsnap.begin_interval_time, 'YYYY-MM-DD')
 );

Enter value for tablespacename: USERS
old 17: and tbl.name ='&TABLESPACENAME'
new 17: and tbl.name ='USERS'

THE_DATE   TABLESPACE SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB..ASED
---------- ---------- ------------- -------------- -----------------
2017-05-10 USERS        4.17
2017-05-11 USERS        4.17         4.17            0
2017-05-12 USERS        4.45         4.17             .28
2017-05-13 USERS        4.55         4.45             .1
2017-05-14 USERS        4.55         4.55            0
2017-05-15 USERS        4.56         4.55             .01
2017-05-16 USERS        4.84         4.56             .28
2017-05-17 USERS        4.96         4.84             .12
2017-05-18 USERS        5.14         4.96             .18
2017-05-19 USERS        5.14         5.14            0

10 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.