Monday, 22 June 2009

Unit size in DBA_HIST_TBSPC_SPACE_USAGE

Question : What is the unit of the below columns in DBA_HIST_TBSPC_SPACE_USAGE?
a. tablespace_size
b. tablespace_maxsize
c. tablespace_usedsize

Answer : They are reprensted in no. of blocks. In other words to get the actual size from this view you need to multiply the values with the DBA_TABLESPACES.BLOCK_SIZE

I used this simple query ( you can tune this query if you see it running slow) to get the tablespace growth report on weekly basis.

select /*+ parallel(c, 5) */
to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name,
round(max(a.tablespace_size*d.block_size)/1024/1024) tblsize_in_MB,
round(max(a.tablespace_maxsize*d.block_size)/1024/1024) totalsize_in_MB,
round(max(a.tablespace_usedsize*d.block_size)/1024/1024) usedsize_in_MB
from DBA_HIST_TBSPC_SPACE_USAGE a, v$tablespace b,
DBA_HIST_SNAPSHOT c, dba_tablespaces d
where a.tablespace_id=b.ts#
and a.snap_id=c.snap_id
and b.name=d.tablespace_name
and b.name in ('USERS')
group by to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name
order by 3,2,1;


Keywords : DBA_HIST_TBSPC_SPACE_USAGE, size, unit

Post a Comment