Monday, June 11, 2012

DBA Script: Track the space utilization of database over time

A script to track the space utilization of your database over time

you must create a table and run this script first

insert into monitor.space_check select a.tablespace_name, sum(a.bytes) free_space,
sysdate timestamp , sum(b.bytes) total_space
from dba_data_files a, dba_free_space b
where a.tablespace_name = b.tablespace_name group by a.tablespace_name;

Then run this script


spool analyze.lis
set pagesize 199
REM set feed off;
PROMPT ************************************
PROMPT * Tablespace Growth Analysis
PROMPT ************************************
column tablespace_name format a15
column col1 format 9999999.99 heading 'Today'
column col2 format 9999999.99 heading '1 wk ago'
column col3 format 999.99 heading '% dif '
column col4 format 9999999.99 heading '2 wks ago'
column col5 format 999.99 heading '% dif '
column col6 format 9999999.99 heading '3 wks ago'
column col7 format 999.99 heading '% dif '
column col8 format 9999999.99 heading '4 wks ago'
column col9 format 999.99 heading '% tot';
select distinct a.tablespace_name,
a.free_space/a.total_space*100 col1,
b.free_space/b.total_space*100 col2,
((a.free_space/a.total_space)/(b.free_space/b.total_space)-1)*100 col3,
c.free_space/c.total_space*100 col4,
((b.free_space/b.total_space)/(c.free_space/c.total_space)-1)*100 col5,
d.free_space/d.total_space*100 col6,
((c.free_space/c.total_space)/(d.free_space/d.total_space)-1)*100 col7,
e.free_space/e.total_space*100 col8,
((b.free_space/b.total_space)/(e.free_space/e.total_space)-1)*100 col9
from monitor.space_check a, monitor.space_check b, monitor.space_check c, monitor.space_check d,
monitor.space_check e where trunc(a.timestamp) = trunc(sysdate)
and trunc(b.timestamp(+)) = trunc(sysdate-7)
and b.tablespace_name (+) = a.tablespace_name
and trunc(c.timestamp(+)) = trunc(sysdate-14)
and c.tablespace_name (+) = a.tablespace_name
and trunc(d.timestamp(+)) = trunc(sysdate-21)
and d.tablespace_name (+) = a.tablespace_name
and trunc(e.timestamp(+)) = trunc(sysdate-28)
and e.tablespace_name (+) = a.tablespace_name
order by tablespace_name
spool off;

No comments:

Post a Comment