Generate tablespace usage trend / growth in hourly manner

Common task of a dba is to monitor the space allocation in tablespaces as they fill and need to add more datafiles in tablespaces  to accommodate new data. so how can a dba guess that what should be enough size of new datafile for a entire day or week. If we have this kind of scripts which can tell us the trend of filling tablespaces then it will be easy for us to add sufficient size datafile. 

When we run this script ,its asks for inatance number. Give 1 if your database is single instance database. In case of RAC ,give the instance number.After instance number it will ask for the ID which is nothing but the TS# in v$tablespace view. In general the tablespace number for system tablespace is 0, for sysaux 1 and for undo its 2 and so on.

Major concept behind this script is DBA view dba_hist_tbspc_space_usage which keeps track of tablespace usage over the time.Here to make this script more useful and specific , v$tablespaces and dba_hist_snapshot tables are also included so that you can view the statistics of space usage of particular tablespace over the time.

SYS> SELECT distinct DHSS.SNAP_ID,VTS.NAME,TO_CHAR(DHSS.END_INTERVAL_TIME, 'DD-MM HH:MI') AS SNAP_Time,ROUND((DHTS.TABLESPACE_USEDSIZE*8192)/1024/1024)/&&max_instance_num AS USED_MB,ROUND((DHTS.TABLESPACE_SIZE*8192)/1024/1024)/&&max_instance_num AS SIZE_MB

FROM DBA_HIST_TBSPC_SPACE_USAGE DHTS,V$TABLESPACE VTS,DBA_HIST_SNAPSHOT DHSS

WHERE VTS.TS#=DHTS.TABLESPACE_ID AND DHTS.SNAP_ID=DHSS.SNAP_ID

AND DHSS.INSTANCE_NUMBER=1 AND TABLESPACE_ID=&id ORDER BY 1;

  SNAP_ID NAME                           SNAP_TIME   USED_MB    SIZE_MB
--------- ------------------------------              -----------            ----------      ----------
        1 USERS                                11-03 10:07         12         13
        2 USERS                                11-03 11:30         12         13
        3 USERS                                12-03 09:45         12         13
        4 USERS                                12-03 10:31         12         13
        5 USERS                                14-03 06:48         12         13
        6 USERS                                14-03 07:30         12         13
        7 USERS                                14-03 08:30         12         13
        8 USERS                                14-03 09:31         12         14
        9 USERS                                14-03 10:30         12         14
       10 USERS                               14-03 11:30         12         14
       11 USERS                               15-03 12:30         12         14

  SNAP_ID NAME                           SNAP_TIME      USED_MB SIZE_MB
--------- ------------------------------                      -----------       ----------    ----------
       12 USERS                                15-03 09:09         12         14
       13 USERS                                15-03 10:30         12         14
       14 USERS                                15-03 08:23         12         14
       15 USERS                                15-03 09:30         12         14
       16 USERS                                15-03 10:30         12         14
       17 USERS                                15-03 11:30         12         14
       18 USERS                                16-03 12:30         12         14
       19 USERS                                16-03 09:02         12         14
       20 USERS                                16-03 10:30         12         14
       21 USERS                                16-03 06:15         12         14
       22 USERS                                16-03 07:30         12         14

  SNAP_ID NAME                           SNAP_TIME      USED_MB SIZE_MB
--------- ------------------------------                -----------             ----------    ----------
       23 USERS                                16-03 08:30         12         14
       24 USERS                                16-03 09:30         12         14
       25 USERS                                16-03 10:30         12         14
       26 USERS                                17-03 08:55         12         14
       27 USERS                                17-03 09:30         12         14
       28 USERS                                17-03 10:30         12         14
       29 USERS                                17-03 06:13         12         14
       30 USERS                                17-03 07:30         12         14

30 rows selected.