Oracle DBMS_ILM
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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_SYSTEM USER_ILMPOLICIES
DBA_OBJECTS DUAL USER_ILMTASKS
DBA_ILMRESULTS ILM_EXECUTION$ USER_ILMRESULTS
DBA_ILMTASKS PLITBLM USER_OBJECTS
DBMS_ILM_ADMIN    
Documented Yes
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.0
Security Model Owned by SYS with execute granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsilm.sql
Subprograms
 
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 (new 12.2)
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

Related Topics
Automatic Data Optimization (ADO)
Built-in Functions
Built-in Packages
DBMS_HEAT_MAP
DBMS_ILM_ADMIN
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved