Tracking Oracle database growth

----------------------/* Tracking Oracle database growth  */-----------------------
The following script can be executed once each week to analyze the table and indexes and collect the table and index data. Note that we must set the oratab file location and pass the proper ORACLE_SID when executing this script:
create table stats$idx_stats as
insert into table stats$idx_stats
(   select
      SYSDATE,
      lower('${host}'),
      lower('${ORACLE_SID}'),
      i.tablespace_name,
      t.table_name,
      i.owner,
      i.index_name,
      i.clustering_factor,
      i.leaf_blocks,
      i.blevel,
      s.next_extent,
      s.extents,
      s.bytes
   from dba_indexes  i,
        dba_segments s,
        dba_tables   t
   where
      i.table_name = t.table_name
   and
      segment_name = index_name
   and
      s.tablespace_name = i.tablespace_name
   and  
      s.owner = i.owner
   and
      i.owner not in ('SYS','SYSTEM')
   and
      t.num_rows > 1000
);
----------------------/* AFTER THAT RUN THE FOLLOWING QUERY   */-----------------
insert into stats$idx_stats
create table stats$idx_stats as
(
   select
      SYSDATE snap_time,
      lower('${host}'),
      lower('${ORACLE_SID}'),
      i.tablespace_name,
      i.owner,
      i.index_name,
      i.clustering_factor,
      i.leaf_blocks,
      i.blevel,
      s.next_extent,
      s.extents,
      s.bytes
   from dba_indexes  i,
        dba_segments s,
        dba_tables   t
   where
      i.table_name = t.table_name
   and
      segment_name = index_name
   and
      s.tablespace_name = i.tablespace_name
   and
      s.owner = i.owner
   and
      i.owner not in ('SYS','SYSTEM')
   and
      t.num_rows > 1000
);

select
   new.index_name,
   old.bytes                old_bytes,
   new.bytes                new_bytes,
   new.bytes - old.bytes    change
from
   stats$idx_stats old,
   stats$idx_stats new
where
   old.index_name = new.index_name
and
   new.bytes > old.bytes
and
   new.bytes - old.bytes > 10000
and
   to_char(new.snap_time, 'YYYY-MM-DD') =
          (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$idx_stats)
and
   to_char(old.snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
and
   new.index_name not like 'STATS$%'
order by
   new.bytes-old.bytes desc
;
----List object growth over last N days, sorted by growth desc

select * from (select c.segment_name "Object Name",b.OWNER,b.CREATED,
                      sum(space_used_delta) / 1024 / 1024  "Growth (MB)"
                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
               group by c.segment_name,b.OWNER,b.CREATED)
               where owner not in ('SYSMAN','SYS')
order by 2 desc;
------------Oracle Database Growth Report by Month and Year
SELECT TO_CHAR(creation_time, 'RRRR Month') "Month",
       SUM(bytes)/1024/1024/1024 "Growth in GB"
  FROM sys.v_$datafile
 WHERE creation_time > SYSDATE-365
 GROUP BY TO_CHAR(creation_time, 'RRRR Month')
 ORDER BY 1
procedure for Calculating Database Growth and scheduling in DBMS JOBS....
1.Create a Table By the Name db_growth...with following details...

Name Null? Type
----------------------------------------- -------- ----------------------------
DAY DATE
DATABASE_SIZE_MB NUMBER
DAILY_GROWTH_MB NUMBER


2.create or replace PROCEDURE database_growth
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d
ay,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size - yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

3.Submit in DBMS_JOBS

variable jobno number;
begin
dbms_job.submit(
:jobno,
'database_growth ;',
trunc(sysdate+1) + 4/24,
'trunc(sysdate+1) + 4/24'
);
commit;
end;