Wie gross sind meine Tablespaces, resp. wieviel Platz habe ich darin noch frei?
Dies muss täglich geprüft werden und bei Bedarf reagiert werden (Tablespace vergrössern)
select dt.tablespace_name as "TS-Name",
substr(dt.status,1,2) || substr(contents,1,1) as "Status",
t.t / (1024*1024) as "Alloc MB",
round(nvl(u.s / (1024*1024),0),2) as "Sum free MB",
round(t.mb / (1024*1024)) as "Sum max MB",
round(case
when ( (t.mb / (1024*1024))-(t.t / (1024*1024))+(nvl(u.s / (1024*1024),0))>= 0)
and (nvl(u.s / (1024*1024),0)-t.mb / (1024*1024) < 0)
then (t.mb / (1024*1024))-(t.t / (1024*1024))+
(nvl(u.s / (1024*1024),0))
else nvl(u.s / (1024*1024),0)
end,2) as "Max free MB",
round(case
when ( (t.mb / (1024*1024))-(t.t / (1024*1024))+(nvl(u.s / (1024*1024),0)) >= 0)
and (nvl(u.s / (1024*1024),0)-t.mb / (1024*1024) < 0)
then ( (t.mb / (1024*1024))-(t.t / (1024*1024))+
(nvl(u.s / (1024*1024),0)))/(t.mb / (1024*1024))*100
else nvl(u.s / (1024*1024),0)/(t.t / (1024*1024))*100
end,2) as "PCT free",
nvl(u.m / (1024*1024),0) as "Max Fragment",
nvl(u.c,0) as "Max #Fragment"
from dba_tablespaces dt,
(select tablespace_name,
sum(bytes) s,
max(bytes) m,
count(*) c
from dba_free_space
group by tablespace_name
) u,
(select tablespace_name,
sum(bytes) t,
sum(maxbytes) mb
from dba_data_files
group by tablespace_name
) t
where dt.tablespace_name = u.tablespace_name(+)
and dt.tablespace_name = t.tablespace_name
order by 7;