How to estimate the historical growth of oracle databases and database objects

How to estimate the historical growth of oracle databases and database objects

----- Database growth in the last seven days. This is only an estimate.

Select sum (space_used_total)/1024/1024/1024 "last 7 days db increase-G"
From
Dba_hist_seg_stat s,
Dba_hist_seg_stat_obj o,
Dba_hist_snapshot sn
Where
S. obj # = o. obj #
And
Sn. snap_id = s. snap_id
And begin_interval_time> sysdate-8
Order
Begin_interval_time
/

Here are two similar script scripts:

Scripts: View historical database Growth

View historical database Growth
In this example, the historical growth of all tablespaces in the database is calculated to calculate the historical growth of the database.
-- Does not include undo and temp

With tmp
(Select rtime,
Sum (tablespace_usedsize_kb) tablespace_usedsize_kb,
Sum (tablespace_size_kb) tablespace_size_kb
From (select rtime,
E. tablespace_id,
(E. tablespace_usedsize) * (f. block_size)/1024 tablespace_usedsize_kb,
(E. tablespace_size) * (f. block_size)/1024 tablespace_size_kb
From dba_hist_tbspc_space_usage e,
Dba_tablespaces f,
V $ tablespace g
Where e. tablespace_id = g. TS #
And f. tablespace_name = g. NAME
And f. contents not in ('temporary ', 'undo '))
Group by rtime)
Select tmp. rtime,
Tablespace_usedsize_kb,
Tablespace_size_kb,
(Tablespace_usedsize_kb-
LAG (tablespace_usedsize_kb, 1, NULL) OVER (order by tmp. rtime) AS DIFF_KB
From tmp,
(Select max (rtime) rtime
From tmp
Group by substr (rtime, 1, 10) t2
Where t2.rtime = tmp. rtime;

-- Including undo and temp

With tmp
(Select min (rtime) rtime,
Sum (tablespace_usedsize_kb) tablespace_usedsize_kb,
Sum (tablespace_size_kb) tablespace_size_kb
From (select rtime,
E. tablespace_id,
(E. tablespace_usedsize) * (f. block_size)/1024 tablespace_usedsize_kb,
(E. tablespace_size) * (f. block_size)/1024 tablespace_size_kb
From dba_hist_tbspc_space_usage e,
Dba_tablespaces f,
V $ tablespace g
Where e. tablespace_id = g. TS #
And f. tablespace_name = g. NAME)
Group by rtime)
Select tmp. rtime,
Tablespace_usedsize_kb,
Tablespace_size_kb,
(Tablespace_usedsize_kb-
LAG (tablespace_usedsize_kb, 1, NULL) OVER (order by tmp. rtime) AS DIFF_KB
From tmp,
(Select min (rtime) rtime
From tmp
Group by substr (rtime, 1, 10) t2 where t2.rtime = tmp. rtime

######################################## ######################
SQL Script: lists the historical changes of the space used by the objects in a snapshot:

Column owner format a16
Column object_name format a36
Column start_day format a11
Column block_increase format 9999999999.

Select obj. owner, obj. object_name,
To_char (sn. BEGIN_INTERVAL_TIME, 'rrrr-MON-DD ') start_day,
Sum (a. db_block_changes_delta) block_increase
From dba_hist_seg_stat,
Dba_hist_snapshot sn,
Dba_objects obj
Where sn. snap_id = a. snap_id
And obj. object_id = a. obj #
And obj. owner not in ('sys ', 'system ')
And end_interval_time between to_timestamp ('17-FEB-2014 ', 'dd-MON-RRRR ')
And to_timestamp ('25-FEB-2014 ', 'dd-MON-RRRR ')
Group by obj. owner, obj. object_name,
To_char (sn. BEGIN_INTERVAL_TIME, 'rrrr-MON-DD ')
Order by obj. owner, obj. object_name;