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

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
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 t.snap_id = u.snap_id
AND ts.ts# = t.ts#
AND t.ts# = u.tablespace_id)
WHERE latest = 1) tsmax
WHERE tsmin.tsname = tsmax.tsname and tsmax.snaptime > tsmin.snaptime)
/
Start Start Curr Curr Alloc Growth Used Growth
Tablespace Alloc (GB) Used (GB) Alloc (GB) Used (GB) % Use GB/day GB/day
---------------- ----------- ---------- ---------- --------- ------------ ------ ------
AMAPS_DATA 1.95 0.00 1.95 0.00 .05 .00 .00
APPS_TS_ARCHIVE 0.73 0.51 0.73 0.52 70.62 .00 .00
APPS_TS_INTERFACE 6.61 5.53 6.61 5.67 85.83 .00 .00
APPS_TS_MEDIA 2.72 2.57 3.78 3.57 94.37 .02 .02
... output omitted for brevity
---------------- ----------- ----------- ---------- --------- ----------- -------- -----
sum 257.77 177.61 303.84 209.90 .95 .66