(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