-- ilm_monitor_report.sql
-- HTML dashboard for DBMS_ILM monitoring with partition growth deltas.
-- Run as a DBA account with access to DBA_* views.

-- ===== User-configurable parameters =====
define REPORT_DIR    = '/u01/app/oracle/admin/reports'
define REPORT_NAME   = 'ilm_report_&&_DATESTAMP..html'
define OWNER_LIKE    = '%PSFT%'                -- filter objects by owner (e.g. 'PSFT' or '%')
define TABLE_LIKE    = '%'                    -- filter table name pattern (e.g. 'PAY_CHECKS%' or '%')
define ARCHIVE_TS    = 'ARCHIVE_TS'           -- archive tablespace name to highlight
define DAYS_BACK     = 30                     -- window for AWR/feature usage context
-- =======================================

column _DATESTAMP new_value _DATESTAMP
select to_char(sysdate,'YYYYMMDD_HH24MISS') as _DATESTAMP from dual;

set termout on pages 0 echo off feedback off verify off trimspool on lines 500 long 100000 longchunksize 100000
set markup html on spool on entmap off preformat off
spool &&REPORT_DIR/&&REPORT_NAME

prompt <style>
prompt body { font-family: system-ui, -apple-system, Segoe UI, Roboto, Arial, sans-serif; margin: 18px; }
prompt h1, h2 { margin-top: 24px; }
prompt table { border-collapse: collapse; width: 100%; margin: 12px 0 24px 0; }
prompt th, td { border: 1px solid #ddd; padding: 6px 8px; vertical-align: top; }
prompt th { background: #f6f8fa; text-align: left; }
prompt .ok { color: #137333; font-weight: 600; }
prompt .warn { color: #b26a00; font-weight: 600; }
prompt .bad { color: #c5221f; font-weight: 600; }
prompt .muted { color: #666; }
prompt .pill { padding: 2px 8px; border-radius: 12px; font-size: 12px; background:#eef2ff; }
prompt .archive { background:#fff7e6; }
prompt </style>

prompt <h1>DBMS_ILM Monitoring & Capacity Report</h1>
prompt <div class="muted">Generated: &&_DATESTAMP</div>

prompt <h2>Environment</h2>
WITH env AS (
  SELECT (SELECT name FROM v$database) db_name,
         (SELECT dbid FROM v$database) dbid,
         (SELECT instance_name FROM v$instance) inst,
         (SELECT host_name FROM v$instance) host,
         (SELECT version FROM v$instance) version,
         SYSDATE now_ts
  FROM dual
)
SELECT * FROM env;

prompt <h2>Heat Map & ADO Status</h2>
SELECT parameter_name, parameter_value
FROM   dba_heat_map_parameters
ORDER  BY parameter_name;

prompt <h2>Feature Usage Signals (Licensing awareness)</h2>
SELECT name,
       detected_usages,
       currently_used,
       TO_CHAR(last_usage_date,'YYYY-MM-DD HH24:MI') last_used
FROM   dba_feature_usage_statistics
WHERE  name IN (
  'Heat Map',
  'Automatic Data Optimization',
  'Advanced Compression',
  'Hybrid Columnar Compression'
)
ORDER BY name;

prompt <h2>Objects Under ILM Management (filter: owner like ''&&OWNER_LIKE'' and table like ''&&TABLE_LIKE'')</h2>
SELECT owner, object_name, object_type, ilm_level, enabled
FROM   dba_ilmobjects
WHERE  owner LIKE '&&OWNER_LIKE'
AND    object_name LIKE '&&TABLE_LIKE'
ORDER BY owner, object_name;

prompt <h2>ILM Policies</h2>
SELECT owner,
       object_name,
       policy_name,
       action,
       scope,
       condition,
       enabled,
       TO_CHAR(created,'YYYY-MM-DD HH24:MI') created
FROM   dba_ilm_policies
WHERE  owner LIKE '&&OWNER_LIKE'
AND    object_name LIKE '&&TABLE_LIKE'
ORDER  BY owner, object_name, policy_name;

prompt <h2>ILM Evaluation Details (why policies fired or not)</h2>
SELECT owner, object_name, policy_name,
       evaluation_result,
       evaluation_reason,
       TO_CHAR(evaluation_time,'YYYY-MM-DD HH24:MI') evaluation_time
FROM   dba_ilmevaluationdetails
WHERE  owner LIKE '&&OWNER_LIKE'
AND    object_name LIKE '&&TABLE_LIKE'
ORDER  BY evaluation_time DESC;

prompt <h2>ILM Jobs (background executions)</h2>
SELECT job_id,
       owner,
       object_name,
       policy_name,
       status,
       TO_CHAR(start_time,'YYYY-MM-DD HH24:MI') start_time,
       TO_CHAR(end_time,'YYYY-MM-DD HH24:MI')   end_time,
       message
FROM   dba_ilmjobs
WHERE  owner LIKE '&&OWNER_LIKE'
AND    object_name LIKE '&&TABLE_LIKE'
ORDER  BY start_time DESC;

prompt <h2>ILM Results</h2>
SELECT job_id,
       owner,
       object_name,
       policy_name,
       action,
       result,
       TO_CHAR(action_time,'YYYY-MM-DD HH24:MI') action_time,
       result_details
FROM   dba_ilmresults
WHERE  owner LIKE '&&OWNER_LIKE'
AND    object_name LIKE '&&TABLE_LIKE'
ORDER  BY action_time DESC;

prompt <h2>Heat Map Activity Snapshot (segment-level)</h2>
SELECT owner,
       segment_name,
       segment_type,
       access_count,
       TO_CHAR(last_access,'YYYY-MM-DD HH24:MI') last_access,
       TO_CHAR(last_modification,'YYYY-MM-DD HH24:MI') last_modification
FROM   dba_heat_map_segment
WHERE  owner LIKE '&&OWNER_LIKE'
AND    segment_name LIKE '&&TABLE_LIKE'
ORDER  BY NVL(last_access, last_modification) DESC NULLS LAST;

prompt <h2>Partition Placement & Size (highlighting ARCHIVE tablespace)</h2>
WITH p AS (
  SELECT p.table_owner owner,
         p.table_name,
         p.partition_name,
         p.tablespace_name,
         NVL(s.bytes,0) bytes
  FROM   dba_tab_partitions p
  LEFT   JOIN dba_segments s
         ON s.owner = p.table_owner
        AND s.segment_name = p.table_name
        AND s.partition_name = p.partition_name
        AND s.segment_type LIKE 'TABLE PARTITION%'
  WHERE  p.table_owner LIKE '&&OWNER_LIKE'
  AND    p.table_name  LIKE '&&TABLE_LIKE'
)
SELECT owner,
       table_name,
       partition_name,
       CASE WHEN tablespace_name = '&&ARCHIVE_TS' THEN
         '<span class="pill archive">'||tablespace_name||'</span>'
       ELSE tablespace_name END as tablespace_name,
       ROUND(bytes/1024/1024) AS mb
FROM   p
ORDER  BY owner, table_name, partition_name;

prompt <h2>Archive Tablespace Usage</h2>
WITH t AS (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 mb_alloc
  FROM   dba_data_files
  GROUP  BY tablespace_name
), f AS (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 mb_free
  FROM   dba_free_space
  GROUP  BY tablespace_name
)
SELECT t.tablespace_name,
       ROUND(t.mb_alloc,0) AS mb_alloc,
       ROUND(NVL(f.mb_free,0),0) AS mb_free,
       ROUND(t.mb_alloc - NVL(f.mb_free,0),0) AS mb_used,
       CASE WHEN t.tablespace_name = '&&ARCHIVE_TS'
            THEN '<span class="pill archive">archive target</span>'
            ELSE '<span class="muted">—</span>' END as note
FROM   t LEFT JOIN f USING (tablespace_name)
ORDER  BY t.tablespace_name;

prompt <h2>Partition Size Snapshot & Growth Since Last Run</h2>
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE ILM_MON_SNAP_PARTS (
      snap_ts         DATE DEFAULT SYSDATE NOT NULL,
      owner           VARCHAR2(128),
      table_name      VARCHAR2(128),
      partition_name  VARCHAR2(128),
      tablespace_name VARCHAR2(128),
      bytes           NUMBER
    )
  ]';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -955 THEN RAISE; END IF;
END;
/

INSERT /*+ append */ INTO ILM_MON_SNAP_PARTS (snap_ts, owner, table_name, partition_name, tablespace_name, bytes)
SELECT SYSDATE,
       p.table_owner,
       p.table_name,
       p.partition_name,
       p.tablespace_name,
       NVL(s.bytes,0)
FROM   dba_tab_partitions p
LEFT   JOIN dba_segments s
       ON s.owner = p.table_owner
      AND s.segment_name = p.table_name
      AND s.partition_name = p.partition_name
      AND s.segment_type LIKE 'TABLE PARTITION%'
WHERE  p.table_owner LIKE '&&OWNER_LIKE'
AND    p.table_name  LIKE '&&TABLE_LIKE';

COMMIT;

WITH latest AS (
  SELECT owner, table_name, partition_name, tablespace_name, bytes,
         snap_ts,
         ROW_NUMBER() OVER (PARTITION BY owner, table_name, partition_name ORDER BY snap_ts DESC) rn
  FROM   ILM_MON_SNAP_PARTS
),
curr AS (
  SELECT * FROM latest WHERE rn = 1
),
prev AS (
  SELECT l1.owner, l1.table_name, l1.partition_name, l1.tablespace_name, l1.bytes bytes_prev, l1.snap_ts snap_prev
  FROM   latest l1
  WHERE  rn = 2
)
SELECT c.owner,
       c.table_name,
       c.partition_name,
       c.tablespace_name,
       ROUND(c.bytes/1024/1024) AS mb_now,
       ROUND(NVL(p.bytes_prev,0)/1024/1024) AS mb_prev,
       ROUND((c.bytes - NVL(p.bytes_prev,0))/1024/1024) AS mb_delta,
       TO_CHAR(NVL(p.snap_prev, c.snap_ts),'YYYY-MM-DD HH24:MI') AS compared_to
FROM   curr c
LEFT   JOIN prev p
       ON p.owner = c.owner
      AND p.table_name = c.table_name
      AND p.partition_name = c.partition_name
ORDER  BY c.owner, c.table_name, c.partition_name;

prompt <h2>Quick Health Flags</h2>
WITH jobs AS (
  SELECT status FROM dba_ilmjobs WHERE start_time > SYSDATE - 7
),
flags AS (
  SELECT
    (SELECT CASE WHEN COUNT(*)=0 THEN 'No recent ILM jobs in last 7 days'
                 WHEN SUM(CASE WHEN status='SUCCEEDED' THEN 1 ELSE 0 END)=0 THEN 'ILM jobs exist but none succeeded'
                 ELSE 'Recent ILM jobs succeeded' END
       FROM jobs) AS job_health,
    (SELECT CASE WHEN (SELECT COUNT(*) FROM dba_ilm_policies WHERE owner LIKE '&&OWNER_LIKE' AND object_name LIKE '&&TABLE_LIKE')=0
                 THEN 'No ILM policies found for current filter'
                 ELSE 'ILM policies present' END
       FROM dual) AS policy_presence,
    (SELECT CASE WHEN (SELECT parameter_value FROM dba_heat_map_parameters WHERE parameter_name='HEAT_MAP') IN ('ON','AUTO')
                 THEN 'Heat Map enabled'
                 ELSE 'Heat Map disabled' END
       FROM dual) AS heat_map_state
  FROM dual
)
SELECT CASE WHEN job_health LIKE 'Recent%' THEN '<span class="ok">'||job_health||'</span>'
            WHEN job_health LIKE 'No recent%' THEN '<span class="warn">'||job_health||'</span>'
            ELSE '<span class="bad">'||job_health||'</span>' END AS job_health,
       CASE WHEN policy_presence LIKE 'ILM policies present' THEN '<span class="ok">'||policy_presence||'</span>'
            ELSE '<span class="warn">'||policy_presence||'</span>' END AS policy_presence,
       CASE WHEN heat_map_state = 'Heat Map enabled' THEN '<span class="ok">'||heat_map_state||'</span>'
            ELSE '<span class="bad">'||heat_map_state||'</span>' END AS heat_map_state
FROM   flags;

prompt <h2>Notes</h2>
prompt <p class="muted">
This report is filtered by owner like '<b>&&OWNER_LIKE</b>' and table like '<b>&&TABLE_LIKE</b>'.
Relocation of partitions using DBMS_ILM MOVE is included with Enterprise Edition. Any compression shown by feature-usage signals
may require Advanced Compression Option; Hybrid Columnar Compression is limited to engineered systems. Verify with your Oracle rep.
</p>

spool off
set markup html off
prompt Report written to &&REPORT_DIR/&&REPORT_NAME