Search Results flush_segment_access




Overview
Explore in AI Chat

Overview

SYS.DBMS_ILM is the Oracle Database-supplied PL/SQL package that implements Information Lifecycle Management (ILM) functionality — the automated tiering, compression, and movement of data across storage tiers according to defined policies. It is an Oracle-supplied SYS-owned package, marked VALID in ETRM, and appears in the Oracle E-Business Suite 12.1.1 and 12.2.2 dependency inventories because the database itself and various EBS components reference it. The package provides the programmatic interface behind Automatic Data Optimization (ADO) and the Heat Map infrastructure, allowing a DBA or application to associate ILM policies with segments, evaluate those policies, and execute the resulting ILM tasks such as compression or data movement to a different segment or tablespace.

In an EBS context, DBMS_ILM is not an Applications product API; it is a database kernel utility whose availability depends on the database release and the ILM/Heat Map feature set. Its presence in ETRM merely confirms the package exists and is valid in the referenced database instance.

Key Procedures and Functions

Twelve documented routines are recorded. Their purposes are as follows:

  • PREVIEW_ILM — Evaluates ILM policies against specified database objects and returns the actions that would be taken, without executing them.
  • EXECUTE_ILM — Evaluates and immediately performs the ILM actions dictated by the applicable policies for the given objects.
  • EXECUTE_ILM_TASK — Runs a previously generated ILM task, as opposed to a fresh policy evaluation.
  • STOP_ILM — Halts ILM task execution, for example to abort a long-running or unexpected data movement.
  • ADD_TO_ILM — Adds a database object or set of objects to ILM management so that policies apply to it.
  • REMOVE_FROM_ILM — Removes an object from ILM management, exempting it from further policy-driven actions.
  • ARCHIVESTATENAME — Returns the archive-state name associated with ILM evaluation, used for reporting and diagnostics.
  • FLUSH_ALL_SEGMENTS — Flushes all collected segment access statistics to the persistent Heat Map repository.
  • FLUSH_SEGMENT_ACCESS — Flushes accumulated access-tracking data for a specific segment.
  • FLUSH_ROWMAPS — Persists all in-memory row-level mapping information used by ILM/ADO.
  • FLUSH_SEGMENT_ROWMAP — Persists row-level mapping information for an individual segment.

The flush routines exist so that Statistics Collection in memory (Heat Map data) can be reliably written to disk before policy evaluation, typically invoked from scheduled jobs.

Tables Accessed

The ETRM dependency extract for this package does not enumerate internal data dictionary tables accessed through APPS synonyms; it records only the internal SYS dependencies (STANDARD, PRVT_ILM) and the public synonym DBMS_ILM that exposes the package. The Heat Map and ILM metadata itself resides in SYS-owned dictionary and repository structures, not in EBS application tables. Consequently, DBMS_ILM does not read or write EBS business tables such as those in AP, GL, or INV. Any statistics it maintains pertain to segment access patterns of database objects generally, including EBS schema objects if those objects have been placed under ILM management.

Usage Notes

DBMS_ILM is invoked directly from SQL*Plus or a scheduled database job (DBMS_SCHEDULER), not from EBS forms or standard concurrent programs. In an EBS environment the typical sequence is: enable Heat Map statistics collection, allow a representative period to elapse, invoke the appropriate FLUSH routines, run PREVIEW_ILM to review candidate actions, and then execute the actions via EXECUTE_ILM or EXECUTE_ILM_TASK. Because ILM and Heat Map are licensed Oracle Database options, DBAs must confirm entitlement before enabling ADO on production EBS databases. Custom code should treat DBMS_ILM as a database service: it is granted to PUBLIC and referenced by PRVT_ILM and other SYS packages, but EBS application-layer patches do not generally call it. Any ILM action against EBS objects should be coordinated with application availability windows, since compression or segment movement can affect performance and must comply with Oracle’s support policy for the EBS database tier.