Oracle | DBMS_ILM

Oracle DBMS_ILM_ADMIN 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 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
ABS_JOBLIMIT	NUMBER	12
JOB_SIZELIMIT	NUMBER	13
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_DEFAULT	NUMBER	-1
ILM_ENABLED	NUMBER	1
ILM_DISABLED	NUMBER	2
ILM_LIMIT_DEF	NUMBER	2
ILM_POLICY_IN_DAYS	NUMBER	0
ILM_POLICY_IN_SECONDS	NUMBER	1
ILM_RETENTION_TIME	NUMBER	30

Dependencies

DBMS_ILM	OBJ$	TABPART$
HEAT_MAP_STAT$	PRVT_ILM	TS$
ILM_PARAM$	TAB$	USER$

Documented Yes: Packages and Types Reference

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

Security Model Owned by SYS with EXECUTE granted to the DBA role

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

Subprograms

CLEAR_HEAT_MAP_ALL	DISABLE_ILM	SET_HEAT_MAP_START
CLEAR_HEAT_MAP_TABLE	ENABLE_ILM	SET_HEAT_MAP_TABLE
CUSTOMIZE_ILM	SET_HEAT_MAP_ALL	ILM Queries

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 dbms_ilm_admin.set_heat_map_start(start_date IN DATE);

exec dbms_ilm_admin.set_heat_map_start(SYSDATE + 1/24);

      *

      ERROR at line 1:

      ORA-38327: invalid argument value

      ORA-06512: at "SYS.DBMS_ILM_ADMIN", line 541

      ORA-06512: at line 1

      
      -- not sure how this can be an invalid argument ... but why argue with an Oracle exception?

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 Queries

ILM Parameter Values SELECT * FROM ilm_param$ ORDER BY 2;