Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)

E17602-14
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

80 DBMS_ILM_ADMIN

The DBMS_ILM_ADMIN package 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.

See Also:

This chapter contains the following topics:


Using DBMS_ILM_ADMIN


Overview

To implement your ILM strategy, you can use Heat Map in Oracle Database to track data access and modification. You can also use Automatic Data Optimization (ADO) to automate the compression and movement of data between different tiers of storage within the database.


Security Model

This package runs under definer's rights. The user requires DBA privileges.


Constants

The DBMS_ILM_ADMIN package uses the constants shown in Table 80-1, "DBMS_ILM_ADM Constants"

Table 80-1 DBMS_ILM_ADM Constants

Constant Value Type Description

EXECUTION_INTERVAL

1

NUMBER

Determines the frequency with which ADO background evaluation occurs. Specified in minutes.

PURGE_INTERVAL

2

NUMBER

Controls the amount of time ADO history should be maintained. Specified in days.

EXECUTION_MODE

4

NUMBER

Controls whether ADO execution is online, offline. The value for this parameter should either be DBMS_ILM_ADMIN.ILM_EXECUTION_OFFLINE or DBMS_ILM_ADMIN.ILM_EXECUTION_ONLINE.

JOBLIMIT

5

NUMBER

Controls the upper limit on number of ADO jobs at any time. The maximum number of concurrent ADO jobs is JOBLIMIT*(number of instances)*(number of CPUs per instance).

ENABLED

7

NUMBER

Provides a way to turn background ADO off or on

TBS_PERCENT_USED

8

NUMBER

Decides when a tablespace is considered full. Specified as a percentage of tablespace quota.

TBS_PERCENT_FREE

9

NUMBER

Decides the targeted tablespace storage through ADO actions as a percentage of tablespace quota.

DEG_PARALLEL

10

NUMBER

Decides the degree of parallelism to be used for ADO jobs

POLICY_TIME

11

NUMBER

Decides if ADO policies are treated as though they are specified in seconds rather than days. Can take value ILM_POLICY_IN_SECONDS (treat policy time in seconds) or ILM_POLICY_IN_DAYS (treat policy time in days - default).

HEAT_MAP_SEG_READ

1

NUMBER

Segment read done

HEAT_MAP_SEG_WRITE

2

NUMBER

Segment write done

HEAT_MAP_SEG_SCAN

4

NUMBER

Full table scan done

HEAT_MAP_SEG_LOOKUP

8

NUMBER

Index scan done


Table 80-2 DBMS_ILM_ADM Constants Used as Parameter Values

Constant Value Type Description

ILM_EXECUTION_OFFLINE

1

NUMBER

Specifies that the object may be offline while ADO action is performed.

ILM_EXECUTION_ONLINE

2

NUMBER

Specifies that the object should be online while ADO action is performed

ILM_ENABLED

4

NUMBER

Indicates automatic ADO policy evaluation and execution is enabled

ILM_DISABLED

2

NUMBER

Indicates automatic ADO policy evaluation and execution is disabled

ILM_POLICY_IN_DAYS

0

NUMBER

Indicates policy is specified in days. This is the default.

ILM_POLICY_IN_SECONDS

1

NUMBER

Indicates policy is specified in seconds (rather than days). This could be used to test ADO policy evaluation quickly instead of waiting for the policy duration.



Summary of DBMS_ILM_ADMIN Subprograms

Table 80-3 DBMS_ILM_ADMIN Package Subprograms

Subprogram Description

CLEAR_HEAT_MAP_ALL Procedure

Deletes all rows except the dummy row

CLEAR_HEAT_MAP_TABLE Procedure

Clears all or some statistics for the heat map table, deleting rows for a given table or segment which match a given pattern, or all such rows

CUSTOMIZE_ILM Procedure

Customizes environment for ILM execution by specifying the values for ILM execution related parameters

DISABLE_ILM Procedure

Turns off all background ILM scheduling

ENABLE_ILM Procedure

Turns on all background ILM scheduling

SET_HEAT_MAP_ALL Procedure

Updates or inserts heat map rows for all tables

SET_HEAT_MAP_START Procedure

Sets the start date for collecting heat map data

SET_HEAT_MAP_TABLE Procedure

Updates or inserts a row for the specified table or segment



CLEAR_HEAT_MAP_ALL Procedure

This procedure deletes all rows in HEAT_MAP_STAT$ except the dummy row.

Syntax

DBMS_ILM_ADMIN.CLEAR_HEAT_MAP_ALL;

CLEAR_HEAT_MAP_TABLE Procedure

This procedure clears all or some statistics for the heat map table, deleting rows for a given table or segment which match a given pattern, or all such rows.

Syntax

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

Parameters

Table 80-4 CLEAR_HEAT_MAP_TABLE Procedure Parameters

Parameter Description

owner

Table owner

tablename

Table name

partition

Name of the subobject, defaults to NULL

access_date

Date for the entry in HEAT_MAP_STAT$ to be removed

segment_access_summary

Summary of segment access constants indicating access operations performed on the segment



CUSTOMIZE_ILM Procedure

This procedure customizes environment for ILM execution by specifying the values for ILM execution related parameters. These values take effect for the next background scheduling.

Syntax

DBMS_ILM_ADMIN.CUSTOMIZE_ILM  (
   parameter            IN       NUMBER,
   value                IN       NUMBER);

Parameters

Table 80-5 CUSTOMIZE_ILM Procedure Parameters

Parameter Description

parameter

One of the parameter constants defined in DBMS_ILM_ADMIN package

value

Value of parameter



DISABLE_ILM Procedure

This procedure turns off all background ILM scheduling.

Syntax

DBMS_ILM_ADMIN.DISABLE_ILM;

ENABLE_ILM Procedure

This procedure turns on all background ILM scheduling.

Syntax

DBMS_ILM_ADMIN.ENABLE_ILM;

SET_HEAT_MAP_ALL Procedure

This procedure updates or inserts heat map rows for all tables.

Syntax

DBMS_ILM_ADMIN.SET_HEAT_MAP_ALL  (
   access_date            IN DATE,
   segment_access_summary IN NUMBER);

Parameters

Table 80-6 SET_HEAT_MAP_ALL Procedure Parameters

Parameter Description

access_date

Date for the entry in HEAT_MAP_STAT$ to be added

segment_access_summary

Summary of segment access constants indicating access operations performed on the segment



SET_HEAT_MAP_START Procedure

This procedure sets the start date for collecting heat map data.

Syntax

DBMS_ILM_ADMIN.SET_HEAT_MAP_START  (
   start_date  IN   DATE);

Parameters

Table 80-7 SET_HEAT_MAP_START Procedure Parameters

Parameter Description

start_date

Indicates the new date from which all statistics are valid



SET_HEAT_MAP_TABLE Procedure

This procedure updates or inserts a row for the specified table or segment.

Syntax

DBMS_ILM_ADMIN.SET_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);

Parameters

Table 80-8 SET_HEAT_MAP_TABLE Procedure Parameters

Parameter Description

owner

Table owner

tablename

Table name

partition

Name of the subobject, defaults to NULL

access_date

Date for the entry in HEAT_MAP_STAT$ to be added

segment_access_summary

Summary of segment access constants indicating access operations performed on the segment