snaps

col today for a32
col alltime for a34
col gap_days for a40
WITH snaps AS (
SELECT snap_id, LAG(snap_id) OVER (ORDER BY snap_id) AS prev_snap_id, end_interval_time
FROM dba_hist_snapshot
WHERE instance_number = USERENV('instance') AND dbid =
(SELECT dbid
FROM v$database) ), gaps AS (
SELECT snap_id, prev_snap_id, TRUNC(end_interval_time) AS gap_day
FROM snaps
WHERE snap_id - prev_snap_id > 1 ), agg AS (
SELECT MAX(CASE WHEN TRUNC(end_interval_time) = TRUNC(SYSDATE) THEN snap_id END) AS t_max, MIN(CASE WHEN TRUNC(end_interval_time) = TRUNC(SYSDATE) THEN snap_id END) AS t_min, MIN(snap_id) AS o_min, MAX(snap_id) AS o_max
FROM snaps ), awr AS (
SELECT extract(day
from snap_interval) * 24 * 60 + extract(hour
from snap_interval) * 60 + extract(minute
from snap_interval) AS snap_Interval_min, extract(day
from retention) + extract(hour
from retention) / 24 + extract(minute
from retention) / 1440 AS ret_days
FROM dba_hist_wr_control )
SELECT 'TODAY awr snapshot:' || t_min || '-' || t_max AS today, 'ALLTIME awr snapshot:' || o_min || '-' || o_max AS alltime, snap_Interval_min, ret_days, CASE WHEN EXISTS
(SELECT 1
FROM gaps) THEN '***GAP' ELSE 'OK' END AS gap,
(SELECT COUNT(*)
FROM gaps) AS gap_cnt,
-- Gap dates without year
(SELECT LISTAGG(TO_CHAR(gap_day, 'DD-MON'),',') WITHIN GROUP (ORDER BY gap_day)
FROM
(SELECT DISTINCT gap_day
FROM gaps) ) AS gap_days
FROM agg, awr;