Enterprise databases accumulate vast amounts of historical data over time journal lines in Oracle E-Business Suite (EBS), payroll checks in PeopleSoft HCM, closed service requests in CRM, or audit logs in custom ERP systems. Left unchecked, this data creates storage bloat, longer backup windows, degraded query performance, and costly migrations. Traditionally, DBAs wrote custom purge scripts to handle “cold” data. But Oracle introduced a smarter, automated way: DBMS_ILM. With DBMS_ILM and the broader Information Lifecycle Management (ILM) framework, you can define policies that automatically compress, move, or archive data as it ages turning Oracle into a self-managing database.

Historical Timeline

• Oracle 11g (2007–2012)
  • ILM was more of a concept than a feature.
  • DBMS_ILM package existed but was very limited, focusing mainly on manual compression/move operations.
• Oracle 12c Release 1 (2013)
  • Heat Map introduced (tracks row/segment usage).
  • Automatic Data Optimization (ADO) became available.
  • DBMS_ILM matured to support policy-driven movement and compression.
  • Licensing Note: Compression features required Advanced Compression Option (ACO).
• Oracle 12c Release 2 (2016)
  • Enhanced policy management, subpartition-level actions.
  • Support for Hybrid Columnar Compression (HCC) in engineered systems.
• Oracle 18c / 19c (2018–2019)
  • DBMS_ILM + ADO stabilized as enterprise-ready.
  • Background ILM jobs integrated with DBMS_SCHEDULER.
  • Adopted widely in ERP/CRM archiving projects.
• Oracle 21c / 23c (2021–2023)
  • Minor refinements but ILM model remained stable.
  • Greater focus on integration with Exadata and Cloud tiering.
  • In 23ai (23c), DBMS_ILM is still the core API for lifecycle policies, with Oracle pushing more cloud-managed automation in Autonomous DB.

Licensing is often misunderstood, so let’s break it down clearly:

Golden Rule:

• Moving cold partitions to an archive tablespace = FREE (EE)
• Compressing cold partitions = PAID (ACO)
• Using Exadata HCC or tiering = Requires Engineered System licensing

Always validate with:

SELECT name, detected_usages, currently_used, last_usage_date
FROM   dba_feature_usage_statistics
WHERE  name LIKE '%Compression%' OR name LIKE '%Heat Map%' OR name LIKE '%ILM%';

This query helps confirm whether your test triggered paid options.

DBMS_ILM Components

• Heat Map
  • Tracks data access (reads/writes).
  • Provides metrics for ILM decisions.
• Automatic Data Optimization (ADO)
  • Executes policies when conditions are met.
  • Works on segments, partitions, or rows.
• DBMS_ILM Package
  • API to create, enable, execute, and monitor ILM policies.
  • Provides manual overrides for testing.

Use Cases (ERP/CRM/PeopleSoft)

1. PeopleSoft Payroll (PAY_CHECKS)
   • Compress after 2 years.
   • Move to ARCHIVE tablespace after 7 years.
2. Oracle EBS GL (GL_JE_LINES)
   • Move journals > 10 years old.
   • Keep recent 3 years online.
3. CRM Tickets
   • Close/compress tickets > 5 years.
   • Keep only 2 years active for customer service SLAs.
4. Custom Banking ERP (TXN_AUDIT_LOG)
   • Auto-compress audit logs after 36 months.
   • Mark READ ONLY after 7 years.

Detailed Demo

6.1. Enable Heat Map

ALTER SYSTEM SET HEAT_MAP=ON SCOPE=BOTH;

6.2. Create Archive Tablespace

CREATE TABLESPACE ARCHIVE_TS 
DATAFILE '/u01/app/oracle/oradata/ARCHIVE01.dbf' SIZE 20G AUTOEXTEND ON;

6.3. Create Partitioned Table

CREATE TABLE psft.pay_checks
(
  check_id    NUMBER,
  emp_id      NUMBER,
  amount      NUMBER,
  check_date  DATE
)
PARTITION BY RANGE (check_date)
(
  PARTITION p2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
  PARTITION p2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
  PARTITION p2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
  PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

6.4. Insert Data

INSERT INTO psft.pay_checks
SELECT level, mod(level,1000), dbms_random.value(1000,5000),
       ADD_MONTHS(SYSDATE, -level)
FROM dual CONNECT BY level <= 200000;
COMMIT;

6.5. Add Policy

BEGIN
  DBMS_ILM.ADD_POLICY(
    object_owner     => 'PSFT',
    object_name      => 'PAY_CHECKS',
    policy_name      => 'PAYROLL_ARCHIVE_POLICY',
    action           => DBMS_ILM.MOVE,
    scope            => DBMS_ILM.SCOPE_PARTITION,
    tier_tablespace  => 'ARCHIVE_TS',
    condition        => 'CREATION_TIME < ADD_MONTHS(SYSDATE, -84)', -- 7 years
    enabled          => DBMS_ILM.ENABLED
  );
END;
/

6.6. Execute Policy

BEGIN
  DBMS_ILM.EXECUTE_ILM(
    object_owner => 'PSFT',
    object_name  => 'PAY_CHECKS',
    policy_name  => 'PAYROLL_ARCHIVE_POLICY'
  );
END;
/

Monitoring ILM

-- check policiess.
SELECT policy_name, action, condition, enabled
FROM   dba_ilm_policies
WHERE  object_name='PAY_CHECKS';

--Monitor Jobs
SELECT job_id, object_name, status, start_time, end_time
FROM   dba_ilmjobs;

-- check results
SELECT * FROM dba_ilmresults WHERE object_name='PAY_CHECKS';

Verify Partition Location

SELECT table_name, partition_name, tablespace_name
FROM   dba_tab_partitions
WHERE  table_name='PAY_CHECKS';

Heat Map Access Tracking

SELECT segment_name, access_count, last_access
FROM   dba_heat_map_segment
WHERE  segment_name='PAY_CHECKS';

Troubleshooting

Best Practices

1. Always partition large tables by date before using ILM.
2. Run in DEV and validate with ERP/CRM reports before PROD rollout.
3. Align with compliance/legal retention rules (SOX, GDPR, HIPAA).
4. Schedule ILM jobs in maintenance windows.
5. Monitor dba_feature_usage_statistics to avoid license violations.
6. Combine ILM with purging strategies for truly obsolete data.

Conclusion

Introduced in Oracle 11g as a concept and matured in 12c, DBMS_ILM is now a stable enterprise feature that brings policy-driven automation to Oracle databases.

• For PeopleSoft Payroll, it can transparently archive checks older than 7 years.
• For EBS Financials, it can compress historical journal entries.
• For CRM logs, it can move cold data to cheaper storage.

When deployed with proper licensing (EE + ACO where needed), DBMS_ILM transforms data management from ad-hoc DBA scripts into a compliant, auditable, and automated lifecycle system.

It is not just a DBA feature… it’s a data governance framework, critical for ERP/CRM modernization and cloud migrations.

If you want to test it, start by enabling Heat Map in your DEV/TEST system, track your largest ERP/CRM tables for 30 days, then design your first MOVE-only ILM policy (no compression). Once comfortable, expand to compression (if licensed) and tiering strategies.

Hope It Helped!
Prashant Dixit
Database Architect @RENAPS
Reach us at : https://renaps.com/