Tablespace usage history of 7 days.
select thedate,
gbsize,
prev_gbsize,
gbsize-prev_gbsize diff
from (
select thedate,
gbsize,
lag(gbsize,1) over (order by r) prev_gbsize
from (
select rownum r,
thedate,
gbsize
from (
select trunc(thedate) thedate,
max(gbsize) gbsize
from (
select to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
from dba_hist_tbspc_space_usage usage,
v$tablespace tablespace,
dba_hist_snapshot snapshot,
v$parameter block_size
where usage.snap_id = snapshot.snap_id
and usage.tablespace_id = tablespace.ts#
and tablespace.name = '&tablespace'
and block_size.name = 'db_block_size'
)
group by
trunc(thedate)
order by
trunc(thedate)
)
)
);
Enter value for Tablespace: TEMP
Sample Output:
THEDATE GBSIZE PREV_GBSIZE DIFF
——— ———- ———– ———-
15-OCT-15 13.04
16-OCT-15 11.21 13.04 -1.83
17-OCT-15 .91 11.21 -10.3
18-OCT-15 0 .91 -.91
19-OCT-15 6.44 0 6.44
20-OCT-15 4.45 6.44 -1.99
21-OCT-15 15.89 4.45 11.44
22-OCT-15 3.88 15.89 -12.01
8 rows selected.