1) check dba_hist_seg_stat
DBMS_SPACE
2) TOAD (DBA=>TABLESPACES => SPACE HISTORY)
3) If you are taking a full database backup through RMAN every night and if your database is 10g with Enterprise manager configured then login to EM DBconsole go to Maintenance tab and backup report link this will show the total database size. So you can know the growth trend this way.
OEM grid control has a Reports tab which gives the details about growth of the database.
Here are the steps to know Database growth pattern for last one month/year using OEM
1) Login to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for last one Month.
4) Also we can get one year Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and click on Oracle Database Tablespace Monthly Space Usage link.
###################################################################
http://www.dba-oracle.com/t_tracking_table_growth.htm
col c1 format a15 heading 'snapshot|date'
col c2 format a25 heading 'table|name'
col c3 format 999,999,999 heading 'space|used|total'
select
to_char(begin_interval_time,'yy/mm/dd hh24:mm') c1,
object_name c2,
space_used_total c3
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
where
o.owner = 'SCHEMA_07'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and
object_name like 'XIF2%'
order by
begin_interval_time;
###############################################################################
*******************************************************************************
a great script to display table size changes between two periods.
===========================================
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - &days_back
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
and c.segment_name = '&segment_name'
group by to_char(end_interval_time, 'MM/DD/YY'))
order by to_date(mydate, 'MM/DD/YY');
***********************************************************************************************************
#####################################################################################
Stephane Faroult offers this extended version of the v$datafile query to track database size over time:
clear columns
set verify off
col tot_mon noprint new_value range
-- Compute how many months have gone since the database was created
select ceil(months_between(sysdate, created)) tot_mon
from v$database
/
col maxinc noprint new_value max_inc
-- Compute the maximum number of times a file created in 'autoextend' mode
-- has grown
select max(round((d.bytes - d.create_bytes) / f.inc / d.block_size)) maxinc
from sys.file$ f,
v$datafile d
where f.inc > 0
and f.file# = d.file#
and d.bytes > d.create_bytes
/
col GB format 9999990.00
col volume format A60
--
-- The factorized subquery tries to build a 'size history' for all
-- files that are in autoextend mode. It generates a list fo file#,
-- associated to 'prior size' and 'new size'. The snag is that a crucial
-- element is missing: the date when autoextension was triggered.
-- To fill the blanks as best as we can, we try to get the creation date
-- of the oldest data or index segment the segment header of which is
-- physically located in the new extension.
--
with extended_files as
(select file#,
nvl(lag(file_size, 1) over (partition by file#
order by file_size), 0)
prior_size,
file_size,
block_size
from (select f.file#,
f.create_blocks + x.rn * f.inc file_size,
f.block_size
from (select f.file#,
d.create_bytes / d.block_size create_blocks,
f.inc,
d.bytes / d.block_size blocks,
d.block_size
from sys.file$ f,
v$datafile d
where f.inc > 0
and f.file# = d.file#
and d.bytes > d.create_bytes
and rownum > 0) f,
(select rownum - 1 rn
from dual
connect by level <= &max_inc + 1) x
where (f.create_blocks + x.rn * f.inc) <= f.blocks))
select "MONTH",
round(cumul/1024, 2) GB,
-- Draw a histogram
rpad('=', round(60 * cumul / current_M), '=') volume
from (select to_char(cal.mon, 'MON-YYYY') "MONTH",
sum(nvl(evt.M, 0)) over (order by cal.mon range unbounded
preceding) cumul,
tot.curr_M current_M,
cal.mon
from -- current database size (data size)
(select round(sum(bytes)/1024/1024) curr_M
from v$datafile) tot,
-- all the months since the database was created
(select add_months(trunc(sysdate, 'MONTH'), -rn) mon
from (select rownum - 1 rn
from dual
connect by level <= &range)) cal,
-- all the months when the size of the database changed
(select size_date,
round(sum(bytes)/1024/1024) M
from (-- files in autoextend mode
select file#, max(bytes) bytes, size_date
from (select file#, bytes, trunc(min(ctime), 'MONTH')
size_date
-- Get the oldest creation date of tables or indexes
-- that are located in extensions.
-- Other segment types are ignored.
from (select s.file#,
f.file_size * f.block_size bytes,
o.ctime
from sys.seg$ s,
extended_files f,
sys.tab$ t,
sys.obj$ o
where s.file# = f.file#
and s.type# = 5
and s.block# between f.prior_size and
f.file_size
and s.file# = t.file#
and s.block# = t.block#
and t.obj# = o.obj#
union all
select s.file#,
f.file_size * f.block_size bytes,
o.ctime
from sys.seg$ s,
extended_files f,
sys.ind$ i,
sys.obj$ o
where s.file# = f.file#
and s.type# = 6
and s.block# between f.prior_size and
f.file_size
and s.file# = i.file#
and s.block# = i.block#
and i.obj# = o.obj#)
group by file#, bytes)
group by file#, size_date
union all
-- files that are not in autoextend mode
select d.file#,
d.create_bytes bytes,
trunc(d.creation_time, 'MONTH') size_date
from v$datafile d,
sys.file$ f
where nvl(f.inc, 0) = 0
and f.file# = d.file#)
group by size_date) evt
where evt.size_date (+) = cal.mon)
order by mon
/
################################################################################################################
******************************************************************************************************************************************
SELECT p.value,
to_char(sysdate,'DD-MM-YYYY'),
d.tablespace_name,
NVL (a.BYTES / 1024 / 1024, 0),
NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024)
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f,
v$parameter p
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
AND p.name like '%instance%name%'
*******************************************************************************************************