Oracle DBMS_ILM
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
Dependencies
DBA_ILMEVALUATIONDETAILS DBMS_ILM_LIB USER_ILMEVALUATIONDETAILS
DBA_ILMPOLICIES DBMS_SYSTEM USER_ILMPOLICIES
DBA_OBJECTS DUAL USER_ILMTASKS
DBA_ILMRESULTS PLITBLM USER_ILMRESULTS
DBA_ILMTASKS PRVT_ILM 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_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
ADO
Automatic Data Optimization
DBMS_HEAT_MAP
DBMS_ILM_ADMIN
Packages

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-2014 Daniel A. Morgan All Rights Reserved