Tablespace usage history

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.

October 22, 2015 · Sanjay · No Comments
Posted in: 1. DB Admin, 4. Performance

Leave a Reply

You must be logged in to post a comment.