1.?推薦一個使用平均值估算表空間的腳本：
--不適用windows
with t1 as (
select ss.run_time,ts.name,
decode((round(su.tablespace_usedsize*dt.block_size/1024/1024,2)),null,0,(round(su.tablespace_usedsize*dt.block_size/1024/1024,2))) used_size_mb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and su.tablespace_id = ts.ts#
and ts.name NOT LIKE '%TEMP%'
and ts.name NOT LIKE '%UNDO%'
and ts.name = dt.tablespace_name order by 2,1),
t2 as (
select e.run_time,e.name,e.used_size_mb,e.used_size_mb - b.used_size_mb growth
from t1 e, t1 b
where e.name = b.name and e.run_time = b.run_time +1),
t5 as (select a.TABLESPACE_NAME,
? ? ? ? ? ? a.FILE_NAME,
? ? ? ? ? ? a.FILE_ID,
? ? ? ? ? ? a.BYTES,
? ? ? ? ? ? a.AUTOEXTENSIBLE,
? ? ? ? ? ? a.ONLINE_STATUS,
? ? ? ? ? ? a.MAXBYTES,
? ? ? ? ? ? case
? ? ? ? ? ? ? when a.AUTOEXTENSIBLE = 'YES' and
? ? ? ? ? ? ? ? ? ? a.ONLINE_STATUS not in ('OFFLINE', 'SYSOFF') then
? ? ? ? ? ? ? ? nvl(a.MAXBYTES, 0)
? ? ? ? ? ? ? else
? ? ? ? ? ? ? ? nvl(a.BYTES, 0)
? ? ? ? ? ? end file_max_size
? ? ? ? from dba_data_files a
? ? ? ? where a.tablespace_name NOT LIKE '%TEMP%'
? ? ? ? and a.tablespace_name NOT LIKE '%UNDO%'
? ? ? ? ),
t3 as (
select tsz.tablespace_name,
tsz.alloc_size_mb,ave.avg_growth_per_day_mb,ave.avg_growth_per_day_mb*90 projected_growth_for_3mths_mb
from
(select tablespace_name, round(sum(file_max_size)/1024/1024,2) alloc_size_mb? from t5 group by tablespace_name) tsz,
(select name,decode(round(avg(growth),2),null,0.11,0,0.11, round(avg(growth),2)) avg_growth_per_day_mb from t2 group by name) ave
where tsz.tablespace_name = ave.name),
t6 as (select
? d.tablespace_name tablespace_name,
? round((d.sumbytes/1024/1024),2) total_g ,
? round(decode(f.sumbytes,null,0,f.sumbytes)/1024/1024,2) free,
? round(((d.sumbytes-f.sumbytes)/1024/1024),6) size_could_be_used,
? round((d.sumbytes-decode(f.sumbytes,null,0,f.sumbytes))*100/d.sumbytes,2) used_pct,
? (100-round((d.sumbytes-decode(f.sumbytes,null,0,f.sumbytes))*100/d.sumbytes,2))*round((d.sumbytes/1024/1024),2) real_free
? from
? ? (select
? ? ? tablespace_name,? sum(bytes) sumbytes
? ? from dba_free_space? group by tablespace_name) f,
? ? (select tablespace_name,? ? ? sum(bytes) sumbytes? ?
? ? ? from dba_data_files? ? group by tablespace_name) d
? ? where f.tablespace_name(+) = d.tablespace_name)
select t4.tablespace_name,decode(t3.alloc_size_mb,null,0,t3.alloc_size_mb) alloc_sz_mb,
--t6.real_free/round(decode(avg_growth_per_day_mb,null,365,0,365,(t3.avg_growth_per_day_mb)),2) Days_To_Be_Used,
((100-decode(round(t6.size_could_be_used*100/t3.alloc_size_mb,2),null,0,round(t6.size_could_be_used*100/t3.alloc_size_mb,2)))/100*t3.alloc_size_mb)/avg_growth_per_day_mb? Days_To_Be_Used,
? round(t6.size_could_be_used*100/t3.alloc_size_mb,4) used_pct_auto,
t6.used_pct used_pct_real
from t3,t6,
(select a.tablespace_name,
round(a.bytes/1024/1024/1024,2) alloc,
round(c.bytes/1024/1024/1024,2) free
from sys.sm$ts_avail a,
sys.sm$ts_free c
where a.tablespace_name = c.tablespace_name(+)
and a.tablespace_name NOT LIKE '%TEMP%'
and a.tablespace_name NOT LIKE '%UNDO%'
) t4
where t4.tablespace_name = t3.tablespace_name(+)
and t4.tablespace_name = t6.tablespace_name(+)
--and ((100-decode(round(t6.size_could_be_used*100/t3.alloc_size_mb,2),null,0,round(t6.size_could_be_used*100/t3.alloc_size_mb,2)))/100*t3.alloc_size_mb)/avg_growth_per_day_mb <=30
and ((100-decode(round(t6.size_could_be_used*100/t3.alloc_size_mb,2),null,0,round(t6.size_could_be_used*100/t3.alloc_size_mb,2)))/100*t3.alloc_size_mb)/avg_growth_per_day_mb>=0
order by 1;
2.?通過線性回歸參數(shù)預(yù)測未來使用量（待補(bǔ)充）：
