Sizing Exadata - Oracle Exadata Recipes: A Problem-Solution Approach - page 123

For the database above, we are experiencing an allocated daily growth rate of 0.95 GB per day and a used growth
rate of 0.66 GB/day, between the oldest and most recent AWR snapshot time range. Unless you're concerned about
per-tablespace growth, you can summarize your growth using the script in Listing 5-18:
Listing 5-18. lst05-18-db-growth-sum.sql
SYS @ PROD> SELECT
ROUND (sum(curr_alloc_gb), 2) curralloc,
ROUND (sum(curr_used_gb), 2) currused,
ROUND (100 * (sum(curr_used_gb) / sum(curr_alloc_gb)), 2) PCTUSED,
ROUND (sum(alloc_gbperday), 2) alloc_gbpd,
ROUND (sum(used_gbperday), 2) used_gbpd
FROM (SELECT tsmin.tsname tbs, tsmin.tablespace_size init_alloc_gb,
tsmin.tablespace_usedsize init_used_gb,
tsmax.tablespace_size curr_alloc_gb,
tsmax.tablespace_usedsize curr_used_gb,
(tsmax.tablespace_size - tsmin.tablespace_size) / (tsmax.snaptime -
tsmin.snaptime) alloc_gbperday,
(tsmax.tablespace_usedsize - tsmin.tablespace_usedsize)
/ (tsmax.snaptime - tsmin.snaptime) used_gbperday
FROM (SELECT *
FROM (SELECT TRUNC (s.begin_interval_time) snaptime,
t.tsname, (ts.BLOCKSIZE * u.tablespace_size) / 1024 / 1024 / 1024
tablespace_size,
(ts.BLOCKSIZE * u.tablespace_usedsize) / 1024 / 1024 / 1024
tablespace_usedsize,
(RANK () OVER (PARTITION BY t.tsname ORDER BY s.snap_id ASC)
) latest,
s.end_interval_time endtime
FROM dba_hist_snapshot s,v$instance i,v$database d,
dba_hist_tablespace_stat t,dba_hist_tbspc_space_usage u,SYS.ts$
ts
WHERE s.snap_id = t.snap_id
AND s.dbid=d.dbid and s.dbid=t.dbid and s.dbid=u.dbid
AND i.instance_number = s.instance_number
AND s.instance_number = t.instance_number
AND ts.ts# = t.ts#
AND t.snap_id = u.snap_id
AND t.ts# = u.tablespace_id)
WHERE latest = 1) tsmin,
(SELECT *
FROM (SELECT TRUNC (s.begin_interval_time) snaptime,
t.tsname,
(ts.BLOCKSIZE * u.tablespace_size) / 1024 / 1024/ 1024
tablespace_size,
(ts.BLOCKSIZE * u.tablespace_usedsize) / 1024 / 1024 / 1024
tablespace_usedsize,
(RANK () OVER (PARTITION BY t.tsname ORDER BY s.snap_id DESC)
) latest,
s.end_interval_time endtime