dba_hist_tbspc_space_usage

This post is also available in: Bulgarian

(Tested on 10.2.0.4)
The following may come handy, if you keep your AWR reports for longer periods. It shows the database growth trend. For example, this is grouped by month:

select tsu.snap_id, to_char(sn.mon, 'Mon.YYYY'), round(sum(tsu.tablespace_size * nvl(ts.blocksize, p.value))/1024/1024/1024, 2) GB_size
  from dba_hist_tbspc_space_usage tsu,
       sys.ts$ ts,
       v$parameter p,
       (select trunc(begin_interval_time, 'MONTH') Mon, min(snap_id) snap_id
          from dba_hist_snapshot
         group by trunc(begin_interval_time, 'MONTH')) sn
 where p.name = 'db_block_size'
   and tsu.tablespace_id = ts.ts#(+)
   and sn.snap_id = tsu.snap_id
 group by tsu.snap_id, sn.mon
 order by 1

A few notes about the query:
– I don’t know why, but the tablespace size is stored in blocks, not bytes. I needed some time to understand the result. The documentation is not quite complete on this topic 🙂
– If you ever had a tablespace, which you dropped afterwards, the query calculates the default default block size. I could not find another way to do this
– Yes, tis is not brilliant too, I know: v$tablespace does not show blocksize, but dba_tablespaces has no ts# column. And because of this… hmmm…. inconsistency, I am using sys.ts$. If you do not like this, you can join v$tablespace and dba_tablespaces using tablespace_name column, which sounds even worse