Database Size and Growth


col allocated_size heading 'Allocated' format 999,999
col used_size heading 'Used' format 999,999
col growth_size heading 'Growth*' format 999,999
select (a.data_size+b.temp_size+c.redo_size)/1024/1024 allocated_size,
d.used_size/1024/1024 used_size,
e.growth_size
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c,
( select sum(bytes) used_size
from dba_segments ) d,
( select round(sum(a.space_used_delta)/1024/1024) growth_size
from dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where (begin_interval_time >= trunc(sysdate-1) and begin_interval_time < trunc(sysdate))
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name ) e
/
prompt *Growth during last calendar day
prompt

Sample Output:

Allocated Used Growth*
--------- -------- --------
994,466 783,455 42,396
*Growth during last calendar day