Oracle | DBMS

Oracle DBMS_ILM Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_ILMDATAMOVEMENTPOLICIES	DBMS_ILM_LIB	PRVT_ILM
DBA_ILMEVALUATION_DETAILS	DBMS_OUTPUT	USER_ILMEVALUATIONDETAILS
DBA_ILMPOLICIES	DBMS_PRIV_CAPTURE	USER_ILMPOLICIES
DBA_OBJECTS	DBMS_SYSTEM	USER_ILMTASKS
DBA_ILMRESULTS	ILM_EXECUTION$	USER_ILMRESULTS
DBA_ILMTASKS	PLITBLM	USER_OBJECTS
DBMS_ILM_ADMIN

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;

      /

      *

      ERROR at line 1:

      ORA-38327: invalid argument value

      ORA-06512: at "SYS.DBMS_ILM", line 51

      ORA-06512: at "SYS.DBMS_ILM", line 190

      ORA-06512: at line 1

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')

      FROM dual;

      

      -- others

      SELECT dbms_ilm.archiveStateName('1')

      FROM dual;

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;

      /

      *

      ERROR at line 1:

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

      ORA-06512: at "SYS.DBMS_ILM", line 462

      ORA-06512: at line 4

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;

      /

      *

      ERROR at line 1:

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

      ORA-06512: at "SYS.DBMS_ILM", line 528

      ORA-06512: at line 4

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;

FLUSH_COL_STATS

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

exec dbms_ilm.flush_col_stats;

FLUSH_ROWMAPS

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

exec dbms_ilm.flush_rowmaps;

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');

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');

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