Oracle Database includes a powerful feature called Heat Map, which tracks how segments are accessed and helps guide Information Lifecycle Management (ILM) decisions. This post walks through a practical SQL script I use to enable Heat Map, capture access data, and generate actionable reports for legacy applications.
A Brief History of Heat Map
Heat Map was first introduced in Oracle Database 12c Release 1 (2013) as part of Oracle’s broader Information Lifecycle Management (ILM) framework.
It was designed to automatically track data access and modification patterns at the segment and row level.
When combined with Automatic Data Optimization (ADO), Heat Map provides the foundation for applying ILM policies, such as moving infrequently used data to lower-cost storage, compressing cold data, or archiving objects that have not been touched in months.
This means Heat Map is not just an observability feature, but a critical enabler of tiered storage strategies and compliance-friendly data management within Oracle Database.
When you inherit or maintain legacy applications, one of the biggest challenges is knowing which tables are actually being used, and how. Heat Map provides visibility into:
- Which tables are “hot” (actively used) vs. “cold” (rarely or never used)
- Whether data is being read by full table scans or index lookups
- Opportunities for compression, archiving, or other ILM policies
Step 1: Enable Heat Map
Heat Map is off by default. Enable it with:
ALTER SYSTEM SET heat_map = ON SCOPE = BOTH;
SHOW PARAMETER heat_map;You can also reset the tracking window to focus on recent usage:
EXEC dbms_ilm_admin.set_heat_map_start(start_date => SYSDATE - 5);
Step 2: Grant Access for Analysis
Run as a DBA to allow schema users to query heat map views:
GRANT SELECT ON dba_segments TO <user>;
GRANT SELECT ON dba_heat_map_segment TO <user>;Step 3: Analyze Object Access Patterns
Heat Map records the last access timestamps for different types of activity, such as segment reads, full scans, index lookups, and writes.
This means the metadata tells you when an object was last touched, not how many times. By comparing these timestamps to the current date, you can classify objects as hot, warm, cool, or cold.
The following query uses a common table expression (CTE) to categorize objects by temperature (recency of access) and access pattern (how they were accessed):
WITH heat_data AS (
SELECT
h.owner,
h.object_name,
o.object_type,
CASE
WHEN h.segment_read_time > SYSDATE - 7 THEN 'HOT'
WHEN h.segment_read_time > SYSDATE - 30 THEN 'WARM'
WHEN h.segment_read_time > SYSDATE - 90 THEN 'COOL'
ELSE 'COLD'
END AS temperature,
CASE
WHEN h.full_scan IS NOT NULL AND h.lookup_scan IS NULL THEN 'FULL_SCAN_ONLY'
WHEN h.full_scan IS NULL AND h.lookup_scan IS NOT NULL THEN 'INDEX_ACCESS_ONLY'
WHEN h.full_scan IS NOT NULL AND h.lookup_scan IS NOT NULL THEN 'MIXED_ACCESS'
ELSE 'NO_ACCESS'
END AS access_pattern,
h.segment_write_time
FROM dba_heat_map_segment h
LEFT JOIN dba_objects o
ON h.owner = o.owner
AND h.object_name = o.object_name
WHERE h.owner = 'SOE'
)
SELECT *
FROM heat_data
ORDER BY temperature, object_type, object_name;This query helps you see whether your schema’s objects are actively accessed, partially used, or not touched at all.
Step 4: ILM Policy Recommendations
Heat Map data can drive ILM decisions. For example:
- Active → keep online
- Compress Candidate → eligible for OLTP or archive compression
- Archive Candidate → move to lower-cost storage
The script provides queries with Heat Map metadata to produce recommendations.
WITH table_sizes AS (
SELECT
owner,
segment_name,
partition_name,
SUM(bytes)/1024/1024 size_mb
FROM dba_segments
WHERE segment_type LIKE 'TABLE%'
-- Add your schema filter here
AND owner = 'SOE'
GROUP BY owner, segment_name, partition_name
),
access_info AS (
SELECT
h.owner,
h.object_name,
h.subobject_name,
h.segment_read_time,
h.segment_write_time,
h.full_scan,
h.lookup_scan,
t.size_mb,
CASE
WHEN h.segment_read_time IS NULL THEN 'NEVER_ACCESSED'
WHEN h.segment_read_time < SYSDATE - 180 THEN 'ARCHIVED_CANDIDATE'
WHEN h.segment_read_time < SYSDATE - 90 THEN 'COMPRESS_CANDIDATE'
WHEN h.segment_read_time < SYSDATE - 30 THEN 'MONITOR'
ELSE 'ACTIVE'
END AS ilm_recommendation
FROM dba_heat_map_segment h
JOIN table_sizes t
ON h.owner = t.owner
AND h.object_name = t.segment_name
AND NVL(h.subobject_name, '-') = NVL(t.partition_name, '-')
WHERE 1 = 1
-- modify for your analysis
AND h.owner = 'SOE'
)
SELECT * FROM access_info
ORDER BY ilm_recommendation, size_mb DESC;Step 5: Build Reusable Reports
For repeatable analysis, the script defines a PL/SQL package heat_map_reports with three procedures:
generate_access_report– Shows recent access historyidentify_unused_objects– Flags objects not touched within a threshold (default: 90 days)suggest_ilm_actions– Lists candidates for compression or archiving
-- Reporting package
-- Still testing so...be careful...
CREATE OR REPLACE PACKAGE heat_map_reports AS
PROCEDURE generate_access_report(p_schema VARCHAR2, p_days NUMBER DEFAULT 30);
PROCEDURE identify_unused_objects(p_schema VARCHAR2, p_threshold_days NUMBER DEFAULT 90);
PROCEDURE suggest_ilm_actions(p_schema VARCHAR2);
END heat_map_reports;
/
CREATE OR REPLACE PACKAGE BODY heat_map_reports AS
PROCEDURE generate_access_report(p_schema VARCHAR2, p_days NUMBER DEFAULT 30) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('=== Heat Map Access Report for ' || p_schema || ' ===');
DBMS_OUTPUT.PUT_LINE('Period: Last ' || p_days || ' days');
DBMS_OUTPUT.PUT_LINE('Generated: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE(CHR(10));
FOR rec IN (
SELECT
object_name,
segment_read_time,
segment_write_time,
ROUND(SYSDATE - NVL(segment_read_time, SYSDATE-9999)) days_unread
FROM dba_heat_map_segment
WHERE owner = p_schema
AND segment_read_time > SYSDATE - p_days
ORDER BY segment_read_time DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(rec.object_name, 30) || ' | ' ||
'Last Read: ' || TO_CHAR(rec.segment_read_time, 'YYYY-MM-DD') || ' | ' ||
'Days Unread: ' || rec.days_unread
);
END LOOP;
END;
PROCEDURE identify_unused_objects(p_schema VARCHAR2, p_threshold_days NUMBER DEFAULT 90) IS
v_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== Unused Objects Report ===');
FOR rec IN (
SELECT
object_name,
segment_read_time,
ROUND(bytes/1024/1024, 2) size_mb
FROM dba_heat_map_segment h
JOIN dba_segments s
ON h.owner = s.owner
AND h.object_name = s.segment_name
WHERE h.owner = p_schema
AND (h.segment_read_time IS NULL
OR h.segment_read_time < SYSDATE - p_threshold_days)
ORDER BY size_mb DESC
) LOOP
v_count := v_count + 1;
DBMS_OUTPUT.PUT_LINE(
rec.object_name || ' (' || rec.size_mb || ' MB) - ' ||
NVL(TO_CHAR(rec.segment_read_time, 'YYYY-MM-DD'), 'NEVER ACCESSED')
);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total unused objects: ' || v_count);
END;
PROCEDURE suggest_ilm_actions(p_schema VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('=== ILM Action Recommendations ===');
-- Archive candidates
DBMS_OUTPUT.PUT_LINE(CHR(10) || '1. ARCHIVE CANDIDATES (>365 days):');
FOR rec IN (
SELECT object_name, ROUND(bytes/1024/1024/1024, 2) size_gb
FROM dba_heat_map_segment h
JOIN dba_segments s ON h.owner = s.owner AND h.object_name = s.segment_name
WHERE h.owner = p_schema
AND h.segment_read_time < SYSDATE - 365
AND bytes > 1024*1024*1024 -- Only objects > 1GB modify as needed
) LOOP
DBMS_OUTPUT.PUT_LINE(' - ' || rec.object_name || ' (' || rec.size_gb || ' GB)');
END LOOP;
-- Compression candidates
DBMS_OUTPUT.PUT_LINE(CHR(10) || '2. COMPRESSION CANDIDATES (90-365 days):');
FOR rec IN (
SELECT object_name, ROUND(bytes/1024/1024, 2) size_mb
FROM dba_heat_map_segment h
JOIN dba_segments s ON h.owner = s.owner AND h.object_name = s.segment_name
WHERE h.owner = p_schema
AND h.segment_read_time BETWEEN SYSDATE - 365 AND SYSDATE - 90
AND bytes > 100*1024*1024 -- Only objects > 100MB
) LOOP
DBMS_OUTPUT.PUT_LINE(' - ' || rec.object_name || ' (' || rec.size_mb || ' MB)');
END LOOP;
END;
END heat_map_reports;
/Example usage:
-- Last 30 days access
EXEC heat_map_reports.generate_access_report('MATT');
-- Unused objects older than 180 days
EXEC heat_map_reports.identify_unused_objects('MATT', 180);
-- ILM actions
EXEC heat_map_reports.suggest_ilm_actions('SOE');Practical Benefits
- Identify “dead” tables before migrations
- Classify hot/cold data for tiered storage
- Support compliance by archiving never-used or stale data
- Guide modernization projects by focusing on actively used objects
Closing Thoughts
Heat Map is an underused but powerful feature in Oracle Database. With just a few SQL queries and a simple reporting package, you can shine a light on legacy schemas and confidently decide what to keep online, compress, or archive.
Full script can be found here ==> https://github.com/oramatt/oracle_heat_map_4_legacy_applications?tab=readme-ov-file
OraMatt: YABAOracle 
Leave a comment