Oracle | DBMS

Oracle DBMS_ILM 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 for implementing Information Lifecycle Management (ILM) strategies using Automatic Data Optimization (ADO) policies.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Row Archival State
ARCHIVE_STATE_ACTIVE	VARCHAR2(1)	'0'
ARCHIVE_STATE_ARCHIVED	VARCHAR2(1)	'1'
Execution Mode
SCOPE_DATABASE	NUMBER	1
SCOPE_SCHEMA	NUMBER	2
Execution Scope
ILM_EXECUTION_OFFLINE	NUMBER	1
ILM_EXECUTION_ONLINE	NUMBER	2
ILM_EXECUTION_DEFAULT	NUMBER	3
Policies
ILM_ALL_POLICIES	VARCHAR2(20)	'ALL POLICIES'
Job Schedule
SCHEDULE_IMMEDIATE	NUMBER	1
ILM Executions (to control resources)
ILM_ALL_EXECUTIONS	NUMBER	-1

Dependencies

DBA_ILMPOLICIES	DBMS_PRIV_CAPTURE	PRVT_ILM
DBA_OBJECTS	DBMS_STANDARD	USER_ILMPOLICIES
DBMS_AUTOIM_INTERNAL	DBMS_SYSTEM	USER_ILMTASKS
DBMS_ILM_ADMIN	ILM_EXECUTION$	USER_ILMRESULTS
DBMS_ILM_LIB	PLITBLM	USER_OBJECTS
DBMS_OUTPUT	PRVT_DBMS_INMEMORY_ADVISOR

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-21700	object does not exist or is marked for delete
ORA-38327	invalid_argument_value
ORA-38328	invalid_ilm_dictionary
ORA-38329	internal_ilm_error
ORA-38330	insufficient_privileges
ORA-38343	unsupported_ilm_supl

First Available 12.1

Security Model Owned by SYS with execute granted to PUBLIC

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

Subprograms

ADD_TO_ILM	FLUSH_ALL_SEGMENTS	FLUSH_SEGMENT_ROWMAP
ARCHIVESTATENAME	FLUSH_COL_STATS	PREVIEW_ILM
EXECUTE_ILM	FLUSH_ROWMAPS	REMOVE_FROM_ILM
EXECUTE_ILM_TASK	FLUSH_SEGMENT_ACCESS	STOP_ILM

ADD_TO_ILM

Add an object to an identified ILM task dbms_ilm.add_to_ilm( task_id IN NUMBER, own IN VARCHAR2, objname IN VARCHAR2, subobjname IN VARCHAR2 DEFAULT NULL);

DECLARE

       th NUMBER;

      BEGIN

        dbms_ilm.preview_ilm(th, dbms_ilm.scope_database);

        dbms_output.put_line(TO_CHAR(th));

        dbms_ilm.add_to_ilm(th, 'UWCLASS', 'SERVERS');

      END;

      /

      *

      ORA-38327: invalid argument value

ARCHIVESTATENAME

Given a value for the ORA_ARCHIVE_STATE column returns value's mapping dbms_ilm.archiveStateName(value IN VARCHAR2) RETURN VARCHAR2;

-- active

      SELECT dbms_ilm.archiveStateName('0');

      

      -- others

      SELECT dbms_ilm.archiveStateName('1');

EXECUTE_ILM

Executes an ADO task for a set of objects without having evaluated them previously

Overload 1 dbms_ilm.execute_ilm( task_id OUT NUMBER, ilm_scope IN NUMBER DEFAULT SCOPE_SCHEMA, execution_mode IN NUMBER DEFAULT ILM_EXECUTION_ONLINE);

DECLARE

       tid NUMBER;

      BEGIN

        dbms_ilm.execute_ilm(tid, dbms_ilm.scope_database);

        dbms_output.put_line(TO_CHAR(tid));

      END;

      /

      *

      ORA-21700: object does not exist or is marked for delete

Executes ADO policies for a specific object

Overload 2 dbms_ilm.execute_ILM( owner IN VARCHAR2, object_name IN VARCHAR2, task_id OUT NUMBER, subobject_name IN VARCHAR2 DEFAULT NULL, policy_name IN VARCHAR2 DEFAULT ILM_ALL_POLICIES, execution_mode IN NUMBER DEFAULT ILM_EXECUTION_ONLINE);

DECLARE

       tid NUMBER;

      BEGIN

        dbms_ilm.execute_ilm('UWCLASS', 'SERVERS', tid);

        dbms_output.put_line(TO_CHAR(tid));

      END;

      /

      *

      ORA-21700: object does not exist or is marked for delete

EXECUTE_ILM_TASK

Execute all ilm policies in a previously evaluated ILM task. The ILM policies are not reevaluated dbms_ilm.execute_ilm_task( task_id IN NUMBER, execution_mode IN NUMBER DEFAULT ILM_EXECUTION_ONLINE, execution_schedule IN NUMBER DEFAULT SCHEDULE_IMMEDIATE);

TBD

FLUSH_ALL_SEGMENTS

Flushes all in-memory segment access tracking information dbms_ilm.flush_all_segments;

exec dbms_ilm.flush_all_segments;

      

      PL/SQL procedure successfully completed.

FLUSH_COL_STATS

Flushes all in-memory column statistics to COLUMN_STAT$ dbms_ilm.flush_col_stats;

exec dbms_ilm.flush_col_stats;

      

      PL/SQL procedure successfully completed.

FLUSH_ROWMAPS

flush in-memory segment rowid bitmaps information dbms_ilm.flush_rowmaps;

exec dbms_ilm.flush_rowmaps;

      

      PL/SQL procedure successfully completed.

FLUSH_SEGMENT_ACCESS

Flushes in-memory access tracking for the specified object dbms_ilm.flush_segment_access( owner_name IN VARCHAR2, object_name IN VARCHAR2, subobject_name IN VARCHAR2 DEFAULT NULL);

conn sys@pdbdev as sysdba

      

      exec dbms_ilm.flush_segment_access('UWCLASS', 'SERVERS');

      

      PL/SQL procedure successfully completed.

FLUSH_SEGMENT_ROWMAP

Flushes the in-memory rowid bitmap for the specified object dbms_ilm.flush_segment_rowmap( owner_name IN VARCHAR2, object_name IN VARCHAR2, subobject_name IN VARCHAR2 DEFAULT NULL);

conn sys@pdbdev as sysdba

      

      exec dbms_ilm.flush_segment_rowmap('UWCLASS', 'SERVERS');

      

      PL/SQL procedure successfully completed.

PREVIEW_ILM

Evaluates all ADO policies in the scope specified by means of an argument dbms_ilm.preview_ilm( task_id OUT NUMBER, ilm_scope IN NUMBER DEFAULT SCOPE_SCHEMA);

DECLARE

       th NUMBER;

      BEGIN

        dbms_ilm.preview_ilm(th, dbms_ilm.scope_database);

        dbms_output.put_line('Task Handle: ' || TO_CHAR(th));

      END;

      /

REMOVE_FROM_ILM

Removes the object specified through the argument from a particular ADO task dbms_ilm.remove_from_ilm( task_id IN NUMBER, own IN VARCHAR2, objname IN VARCHAR2, subobjname IN VARCHAR2 DEFAULT NULL);

TBD

STOP_ILM

Stops ADO-related jobs created for a particular ADO task dbms_ilm.stop_ilm ( task_id IN NUMBER, p_drop_running_jobs IN BOOLEAN DEFAULT FALSE);

TBD