Oracle DBMS_ILM_ADMIN
Version 12.1.0.1

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 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
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
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_ENABLED NUMBER 1
ILM_DISABLED NUMBER 2
ILM_LIMIT_DEF NUMBER 10
ILM_POLICY_IN_DAYS NUMBER 0
ILM_POLICY_IN_SECONDS NUMBER 1
Dependencies
DBMS_ILM OBJ$ TABPART$
HEAT_MAP_STAT$ PRVT_ILM TS$
ILM_PARAM$ TAB$ USER$
Documented Yes
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.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsilm.sql
Subprograms
 
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;
 
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$');
 
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_ILM
Disable ILM dbms_ilm_admin.disable_ilm;
exec dbms_ilm_admin.disable_ilm;
 
ENABLE_ILM
Enable ILM dbms_ilm_admin.enable_ilm;
exec dbms_ilm_admin.enable_ilm;
 
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

SR opened 20 July, 2013: Does not work in the released product as seen in the demo at right
dbms_ilm_admin.set_heat_map_start(start_date IN DATE);
exec dbms_ilm_admin.set_heat_map_start(SYSDATE);
*
ERROR at line 1:
ORA-38327: invalid argument value
ORA-06512: at "SYS.DBMS_ILM_ADMIN", line 530
ORA-06512: at line 1
 
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);
 
ILM Related Queries
ILM Parameter Values SELECT *
FROM ilm_param$
ORDER BY 2;

Related Topics
DBMS_HEAT_MAP
DBMS_ILM
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