Howdy,
In beta 12.12.0.8 and 12.12.0.11, I’m running this SQL:
*WARNING! SQL WILL TRIGGER THE LICENSED DIAGNOSTICS PACK USAGE IF RUN! *(Remove “_REMOVETHISTORUN” from the SQL to run successfully. Thanks, Oracle Corp.)
SELECT object_name,
snap_time,
space_allocated_total,
projected_allocation
FROM dba_hist_seg_stat_REMOVETHISTORUN s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
WHERE o.tablespace_name = :ts_name AND
s.obj# = o.obj#
AND s.dataobj# = o.dataobj#
AND s.dbid = o.dbid
AND sn.snap_id = s.snap_id
AND sn.dbid = s.dbid
AND sn.instance_number = s.instance_number
AND s.space_allocated_delta != 0
AND o.object_name = :table_name
AND TO_CHAR(sn.begin_interval_time,‘YYYYMMDD’) > :yyyymmdd
MODEL
DIMENSION BY
(
o.object_name,
TO_CHAR(sn.begin_interval_time,‘MM/DD/YYYY HH24:MI:SS’) “SNAP_TIME”
)
MEASURES
(
s.space_allocated_total,
CAST(NULL AS NUMBER) “PROJECTED_ALLOCATION”,
TO_NUMBER(TO_CHAR(sn.begin_interval_time,‘J’)) + (TO_NUMBER(TO_CHAR(sn.begin_interval_time,‘SSSSS’))/(246060)) “SNAP_JULIAN”
)
RULES UPSERT
(
projected_allocation[:table_name, snap_time] =
TRUNC
(
(REGR_SLOPE(space_allocated_total, snap_julian)[CV(), ANY] * snap_julian[CV(), CV()])
+ REGR_INTERCEPT(space_allocated_total, snap_julian)[CV(), ANY]
)
)
ORDER BY 1,2;
The ANY wildcard specification gets flagged as being invalid, but it isn’t. The documentation on it ain’t great, but I did manage to find it at https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#GUID-CC847DA6-8838-412B-B984-AF75EA0FFAC8 And as I was using this SQL in Oracle 10.1, it’s been around awhile…
Thanks!
Rich
Disclaimer: I wrote the SQL 10 years ago, so don’t expect me to be able to explain it.