87 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.
This chapter contains the following topics:
See Also:
-
Oracle Database VLDB and Partitioning Guide for information about managing Automatic Data Optimization (ADO) with this package
87.1 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.
87.2 DBMS_ILM_ADMIN Security Model
This package runs under definer's rights. The user requires DBA privileges.
87.3 DBMS_ILM_ADMIN Constants
The table in this topic describes constants used by the DBMS_ILM_ADMIN
package.
The value column refers to the numeric or character value that the constants resolve to.
Table 87-1 DBMS_ILM_ADMIN Constants
Constant | Value | Type | Description |
---|---|---|---|
|
|
|
Specifies the absolute number of concurrent ILM ADO jobs. |
|
1 |
|
Determines the frequency with which ADO background evaluation occurs. Specified in minutes. |
|
|
|
Controls the amount of time ADO history should be maintained. Specified in days. |
|
4 |
|
Controls whether ADO execution is online, offline. The value for this parameter should either be |
|
|
|
Controls the upper limit on number of ILM ADO jobs at any time. The maximum number of concurrent ADO jobs is |
|
|
|
Specifies the size (in megabytes) of the data that is processed by a single ILM ADO row level compression job. |
|
|
|
Provides a way to turn background ADO off or on |
|
|
|
Decides when a tablespace is considered full. Specified as a percentage of tablespace quota. |
|
|
|
Decides the targeted tablespace storage through ADO actions as a percentage of tablespace quota. |
|
|
|
Decides the degree of parallelism to be used for ADO jobs |
|
11 |
|
Decides if ADO policies are treated as though they are specified in seconds rather than days. Can take value |
|
1 |
|
Segment read done |
|
2 |
|
Segment write done |
|
4 |
|
Full table scan done |
|
8 |
|
Index scan done |
The DBMS_ILM_ADMIN
package uses the constants as parameter values shown in Table 87-2.
Table 87-2 DBMS_ILM_ADMIN Constants Used as Parameter Values
Constant | Value | Type | Description |
---|---|---|---|
|
1 |
|
Specifies that the object may be offline while ADO action is performed. |
|
|
|
Specifies that the object should be online while ADO action is performed |
|
1 |
|
Indicates automatic ADO policy evaluation and execution is enabled |
|
|
|
Indicates automatic ADO policy evaluation and execution is disabled |
|
|
|
Indicates policy is specified in days. This is the default. |
|
|
|
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. |
87.4 Summary of DBMS_ILM_ADMIN Subprograms
This table lists and briefly describes the DBMS_ILM_ADMIN
package subprograms.
Table 87-3 DBMS_ILM_ADMIN Package Subprograms
Subprogram | Description |
---|---|
Deletes all rows except the dummy row |
|
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 |
|
Customizes environment for ILM execution by specifying the values for ILM execution related parameters |
|
Turns off all background ILM scheduling |
|
Turns on all background ILM scheduling |
|
Updates or inserts heat map rows for all tables |
|
Sets the start date for collecting heat map data |
|
Updates or inserts a row for the specified table or segment |
87.4.1 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;
87.4.2 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 87-4 CLEAR_HEAT_MAP_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Table owner |
|
Table name |
|
Name of the subobject, defaults to |
|
Date for the entry in |
|
Summary of segment access constants indicating access operations performed on the segment |
87.4.3 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 87-5 CUSTOMIZE_ILM Procedure Parameters
Parameter | Description |
---|---|
|
One of the parameter constants defined in |
|
Value of parameter |
87.4.4 DISABLE_ILM Procedure
This procedure turns off all background ILM scheduling.
Syntax
DBMS_ILM_ADMIN.DISABLE_ILM;
87.4.5 ENABLE_ILM Procedure
This procedure turns on all background ILM scheduling.
Syntax
DBMS_ILM_ADMIN.ENABLE_ILM;
87.4.6 SET_HEAT_MAP_ALL Procedure
This procedure sets an HTTP request header. The request header is sent to the Web server as soon as it is set.
Syntax
DBMS_ILM_ADMIN.SET_HEAT_MAP_ALL ( access_date IN DATE, segment_access_summary IN NUMBER);
Parameters
Table 87-6 SET_HEAT_MAP_ALL Procedure Parameters
Parameter | Description |
---|---|
|
Date for the entry in |
|
Summary of segment access constants indicating access operations performed on the segment |
87.4.7 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 87-7 SET_HEAT_MAP_START Procedure Parameters
Parameter | Description |
---|---|
|
Indicates the new date from which all statistics are valid |
87.4.8 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 87-8 SET_HEAT_MAP_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Table owner |
|
Table name |
|
Name of the subobject, defaults to |
|
Date for the entry in |
|
Summary of segment access constants indicating access operations performed on the segment |