Oracle | DBMS_ILM

Oracle DBMS_ILM_ADMIN Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Provides an interface to customize Automatic Data Optimization (ADO) policy execution. In combination with partitioning and compression, ADO policies can be used to help implement an Information Lifecycle Management (ILM) strategy.

AUTHID DEFINER

Constants

Name	Data Type	Value
Auto Optimize
AUTO_OPTIMIZE_OFF	NUMBER	0
AUTO_OPTIMIZE_ON	NUMBER	1
AUTO_OPTIMIZE_ALLOW	NUMBER	2
Execution Parameters
EXECUTION_INTERVAL	NUMBER	1
PURGE_INTERVAL	NUMBER	2
EXECUTION_MODE	NUMBER	4
JOBLIMIT	NUMBER	5
ENABLED	NUMBER	7
TBS_PERCENT_USED	NUMBER	8
TBS_PERCENT_FREE	NUMBER	9
DEG_PARALLEL	NUMBER	10
POLICY_TIME	NUMBER	11
ABS_JOBLIMIT	NUMBER	12
JOB_SIZELIMIT	NUMBER	13
AUTO_OPTIMIZE_INACTIVITY_THRESHOLD	NUMBER	14
AUTO_OPTIMIZE_ENABLED	NUMBER	15
Execution Modes
ILM_EXECUTION_OFFLINE	NUMBER	1
ILM_EXECUTION_ONLINE	NUMBER	2
ILM_EXECUTION_DEFAULT	NUMBER	3
Heat Map Segment Access
HEAT_MAP_SEG_WRITE	NUMBER	1
HEAT_MAP_SEG_READ	NUMBER	2
HEAT_MAP_SEG_SCAN	NUMBER	4
HEAT_MAP_SEG_LOOKUP	NUMBER	8
Miscellaneous
ILM_DEFAULT	NUMBER	-1
ILM_ENABLED	NUMBER	1
ILM_DISABLED	NUMBER	2
ILM_LIMIT_DEF	NUMBER	2
ILM_POLICY_IN_DAYS	NUMBER	0
ILM_POLICY_IN_SECONDS	NUMBER	1
ILM_RETENTION_TIME	NUMBER	30
AUTO_OPTIMIZE_INACTIVITY_ THRESHOLD_DEFAULT	NUMBER	1440

Dependencies

DBA_OBJECTS	ILM_PARAM$	TABSUBPART$
DBMS_AUTO_TASK_ADMIN	OBJ$	TS$
DBMS_ILM	PLITBLM	USER$
HEAT_MAP_STAT$	PRVT_ILM	V$PARAMETER
ILMOBJ$	TAB$	X$KTSVSSI
ILMCONCURRENCY$	TABPART$

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
-38327	invalid_argument_value
-38328	invalid_ilm_dictionary state
-38330	insufficient_privileges
-38343	ADO online mode unsupported with Supplemental Logging

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmsilm.sql

Subprograms

ALLOW_AUTO_OPTIMIZE	DISABLE_ILM	RESUME_AUTO_OPTIMIZE
CLEAR_HEAT_MAP_ALL	ENABLE_AUTO_OPTIMIZE	SET_HEAT_MAP_ALL
CLEAR_HEAT_MAP_TABLE	ENABLE_ILM	SET_HEAT_MAP_START
CUSTOMIZE_ILM	IGNORE_AUTO_OPTIMIZE_CRITERIA	SET_HEAT_MAP_TABLE
DISABLE_AUTO_OPTIMIZE	PAUSE_AUTO_OPTIMIZE	ILM Queries

ALLOW_AUTO_OPTIMIZE (new 23ai)

Undocumented dbms_ilm_admin.allow_auto_optimize;

exec dbms_ilm_admin.allow_auto_optimize;

      

      PL/SQL procedure successfully completed.

CLEAR_HEAT_MAP_ALL

Delete all heat map rows except the dummy row dbms_ilm_admin.clear_heat_map_all;

exec dbms_ilm_admin.clear_heat_map_all;

      

      PL/SQL procedure successfully completed.

CLEAR_HEAT_MAP_TABLE

Clear all or some statistics for a table: deletes rows for the that matches a given pattern or all such rows dbms_ilm_admin.clear_heat_map_table( owner IN VARCHAR2, tablename IN VARCHAR2, partition IN VARCHAR2 DEFAULT '', access_date IN DATE DEFAULT NULL, segment_access_summary IN NUMBER DEFAULT NULL);

exec dbms_ilm_admin.clear_heat_map_table('SYS', 'OBJ$');

      

      PL/SQL procedure successfully completed.

CUSTOMIZE_ILM

Customize ILM execution environment that take effect for the next scheduled run dbms_ilm_admin.customize_ilm( parameter IN NUMBER, value IN NUMBER);

col param_name format a25

      

      SELECT *

      FROM ilm_param$;

      

      DECLARE

       param NUMBER := dbms_ilm_admin.joblimit;

       pval   NUMBER := 9;

      BEGIN

        dbms_ilm_admin.customize_ilm(param, pval);

        dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used, 90);

        dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_free, 20);

        dbms_ilm_admin.customize_ilm(dbms_ilm_admin.enabled, 15);

      END;

      /

      

      SELECT *

      FROM ilm_param$;

DISABLE_AUTO_OPTIMIZE (new 23ai)

Undocumented dbms_ilm_admin.disable_auto_optimize;

exec dbms_ilm_admin.disable_auto_optimize;

      

      PL/SQL procedure successfully completed.

DISABLE_ILM

Disable ILM dbms_ilm_admin.disable_ilm;

exec dbms_ilm_admin.disable_ilm;

      

      PL/SQL procedure successfully completed.

ENABLE_AUTO_OPTIMIZE (new 23ai)

Undocumented dbms_ilm_admin.enable_auto_optimize;

exec dbms_ilm_admin.enable_auto_optimize;

ENABLE_ILM

Enable ILM dbms_ilm_admin.enable_ilm;

exec dbms_ilm_admin.enable_ilm;

      

      PL/SQL procedure successfully completed.

IGNORE_AUTO_OPTIMIZE_CRITERIA (new 23ai)

Undocumented dbms_ilm_admin.ignore_auto_optimize_criteria( owner_name IN VARCHAR2, obj_name IN VARCHAR2, subobj_name IN VARCHAR2 DEFAULT NULL);

TBD

PAUSE_AUTO_OPTIMIZE (new 23ai)

Undocumented dbms_ilm_admin.pause_auto_optimize;

exec dbms_ilm_admin.pause_auto_optimize;

      

      PL/SQL procedure successfully completed.

RESUME_AUTO_OPTIMIZE (new 23ai)

Undocumented dbms_ilm_admin.resume_auto_optimize;

exec dbms_ilm_admin.resume_auto_optimize;

SET_HEAT_MAP_ALL

Update or insert heat map rows for all segments dbms_ilm_admin.set_heat_map_all( access_date IN DATE, segment_access_summary IN NUMBER);

exec dbms_ilm_admin.set_heat_map_all(SYSDATE, 1);

SET_HEAT_MAP_START

Sets the start date for collecting heat map data dbms_ilm_admin.set_heat_map_start(start_date IN DATE);

exec dbms_ilm_admin.set_heat_map_start(SYSDATE + 1/24);

      *

      ORA-38327: invalid argument value

      

      
      -- not sure how this can be an invalid argument ... but why argue with an exception?

SET_HEAT_MAP_TABLE

Update or insert heat map rows for the identified segment dbms_ilm_admin.set_heat_map_table( owner IN VARCHAR2, tablename IN VARCHAR2, partition IN VARCHAR2 DEFAULT '', access_date IN DATE, segment_access_summary IN NUMBER);

exec dbms_ilm_admin.set_heat_map_table(USER, 'OBJ$', NULL, SYSDATE, 1);

      

      PL/SQL procedure successfully completed.

ILM Queries

ILM Parameter Values SELECT * FROM ilm_param$ ORDER BY 2;