Tablespace Usage History Report

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

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

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING
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_INCREASED
---------- ----------- ------------- -------------- --------------------
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.
Advertisements
This entry was posted in AWR, SQL Scripts For DBA and tagged , , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s