思路与方法
1、基于dba_hist_tbspc_space_usage计算表空间日增长，计算出2个值近3天平均日增长，近一天增长。
2、基于dba_hist_tbspc_space_usage查询最后一次采样，计算表空间使用情况，剩余空间，使用率。
3、有了剩余空间，增长速率制定规则，估算可用天数，按以下顺序评估
当近3天增长大于0时，使用最近3天平均增长估算
ELSE
当近1天增长大于0时，使用最近1天平均增长估算
ELSE
使用NULL值估算，因为负数（进行过数据清理）或0增长，没有办法估算可用天数
实践
根据以上规则，编写角本，使时可根据维护的数据库的情况微调
with bsz as (select to_number(value) bsz from V$SPPARAMETER where name='db_block_size')
,ht as
(select SNAP_ID,
TABLESPACE_ID,
round(TABLESPACE_SIZE * bsz.bsz / 1024 / 1024 / 1024) total_space,
round(TABLESPACE_USEDSIZE * bsz.bsz / 1024 / 1024 / 1024) used_space
from dba_hist_tbspc_space_usage,bsz),
hist_tbspc_usage as
(select d.dbid,
d.name db_name,
cast(END_INTERVAL_TIME as date) exectime,
c.name tablespace_name,
a.total_space,
used_space,
total_space - used_space free_space,
round(used_space/greatest(total_space,1),2) used_percents
from ht a, dba_hist_snapshot b, v$tablespace c, v$database d
where b.instance_number = 1
and a.snap_id = b.snap_id
and a.TABLESPACE_ID = c.ts#
and b.END_INTERVAL_TIME > sysdate -3
order by 2, 1),
tlast3day as
(select exectime,
dbid,
db_name,
tablespace_name,
used_space,
used_space - lag(used_space) over(partition by dbid, db_name, tablespace_name order by exectime) used_diff,
(exectime - lag(exectime)
over(partition by dbid, db_name, tablespace_name order by exectime)) tm
from hist_tbspc_usage
where exectime >= sysdate - 3)
--select *from tlast3day
,t3day as
(select dbid,
db_name,
tablespace_name,sum(used_diff),
sum(tm),count(*),
round(sum(used_diff) / decode(sum(tm),0,1,sum(tm)), 3) avg_used
from tlast3day
where used_diff is not null
group by dbid, db_name, tablespace_name)
--select * from t3day
,
tlastday as
(select exectime,
db_name,
dbid,
tablespace_name,
used_space,
used_space - lag(used_space) over(partition by dbid, db_name, tablespace_name order by exectime) used_diff,
(exectime - lag(exectime)
over(partition by dbid, db_name, tablespace_name order by exectime)) tm
from hist_tbspc_usage
where exectime >= sysdate - 1) ,
t1day as
(select dbid,
db_name,
tablespace_name,
round(sum(used_diff) / decode(sum(tm),0,1,sum(tm)), 3) avg_used
from tlastday
where used_diff is not null
group by dbid, db_name, tablespace_name),
t3 AS
(SELECT *
FROM hist_tbspc_usage
WHERE exectime > (select max(exectime)-10/1440 from hist_tbspc_usage)
and exectime < sysdate),
t4 AS
(SELECT a.dbid,
a.db_name,
a.tablespace_name,
a.total_space,
a.used_space,
a.free_space,
a.used_percents,
TO_CHAR(a.exectime, 'yyyy-mm-dd hh24:mi') exectime,
ROUND(b.avg_used, 2) Daily3_usage,
ROUND(c.avg_used, 2) Daily1_usage,
ROUND(a.free_space / (case when b.avg_used>0 then b.avg_used when c.avg_used>0 then c.avg_used else null end), 1) EST_days
FROM t3 a, t3day b, t1day c
WHERE a.dbid = b.dbid
and a.db_name = b.db_name
AND a.tablespace_name = b.tablespace_name
and a.dbid = c.dbid
and a.db_name = c.db_name
and a.tablespace_name = c.tablespace_name)
SELECT db_name,
tablespace_name,
total_space as "Total(GB)",
used_space as "USED(GB)",
free_space as "Free(GB)",
(100 - used_percents) "Used %",
exectime as sameple_time,
daily3_usage,
daily1_usage,
--aa,
est_days
FROM t4
ORDER BY EST_days;示例输出，注意当DAILY1_USAGE明显大于DAILY3_USAGE时可疑识别在加速增长
DAILY3_USAGE 近3天平均日增长(GB)
DAILY1_USAGE 近1天平均日增长(GB)
EST_DAYS 估算的可用天数
DB_NAME TABLESPACE_NAME Total(GB) USED(GB) Free(GB) Used % SAMEPLE_TIME DAILY3_USAGE DAILY1_USAGE EST_DAYS
--------- ------------------------------ ---------- ---------- ---------- ---------- ---------------- ------------ ------------ ----------
TBCSA IDX111 1319 1303 16 99.01 2023-02-28 09:00 .67 1.02 23.8
TBCSA IDX222 1080 1069 11 99.01 2023-02-28 09:00 .34 1.02 32.7
TBCSA IDX333 935 900 35 99.04 2023-02-28 09:00 .67 1.02 52.2
TBCSA DATA111 2715 2382 333 99.12 2023-02-28 09:00 5.71 9.19 58.4
TBCSA DATA222 3225 3086 139 99.04 2023-02-28 09:00 2.35 2.04 59.1
TBCSA USERS 607 547 60 99.1 2023-02-28 09:00 1.01 4.09 59.6
TBCSA IDXCOMMON 195 173 22 99.11 2023-02-28 09:00 .34 1.02 65.5
TBCSA DATA333 1095 1026 69 99.06 2023-02-28 09:00 1.01 2.04 68.5
TBCSA DATA444 3968 3728 240 99.06 2023-02-28 09:00 3.36 4.09 71.5
TBCSA DATA555 1860 1628 232 99.12 2023-02-28 09:00 3.02 3.06 76.8
TBCSA DATA666 495 434 61 99.12 2023-02-28 09:00 .67 0 90.9
TBCSA UNDOTBS2 150 25 125 99.83 2023-02-28 09:00 1.34 11.23 93.1
TBCSA DATA777 510 407 103 99.2 2023-02-28 09:00 1.01 1.02 102.3
TBCSA IDX444 1005 970 35 99.03 2023-02-28 09:00 .34 1.02 104.2
TBCSA IDX555 255 194 61 99.24 2023-02-28 09:00 .34 0 181.5
TBCSA IDX666 165 97 68 99.41 2023-02-28 09:00 .34 0 202.4
TBCSA IDX777 390 302 88 99.23 2023-02-28 09:00 .34 0 261.9
TBCSA DATA777 900 810 90 99.1 2023-02-28 09:00 .34 0 267.9
TBCSA DATA888 668 468 200 99.3 2023-02-28 09:00 .34 1.02 595.2
TBCSA DATACOMMON 705 404 301 99.43 2023-02-28 09:00 .34 1.02 895.8
TBCSA DATA999 1215 769 446 99.37 2023-02-28 09:00 .34 0 1327.4
TBCSA SYSTEM 30 4 26 99.87 2023-02-28 09:00 0 0
TBCSA DATETEST_SPACE 360 5 355 99.99 2023-02-28 09:00 0 0
TBCSA DATA000 600 442 158 99.26 2023-02-28 09:00 0 0
TBCSA UNDOTBS1 150 22 128 99.85 2023-02-28 09:00 0 -3.06
TBCSA SYSAUX 60 41 19 99.32 2023-02-28 09:00 -.34 0
...
37 rows selected.
