Search Results dbms_refresh_exp_lwm

Overview

SYS.DBMS_REFRESH_EXP_LWM is an Oracle-supplied PL/SQL package body resident in the SYS schema. Its name identifies it as part of the materialized view refresh infrastructure — specifically the "EXP" (export/explain or expansion) variant associated with low-water-mark (LWM) tracking of refresh groups. In an Oracle E-Business Suite 12.1.1 or 12.2.2 environment, this package operates silently behind the database's replication and snapshot management layer. It supports the internal mechanics used by DBMS_REFRESH to maintain refresh groups, which are collections of materialized views refreshed together as a single transactional unit. EBS installations frequently rely on such groups (for example, in Discoverer summary management, custom data marts, or the interfaces feeding the Daily Business Intelligence schemas), so DBMS_REFRESH_EXP_LWM participates indirectly in keeping derived data sets synchronized with their base tables.

Because the object resides in SYS and is marked VALID but carries no documented procedures or functions in the ETRM metadata, it is classified as an internal, non-public API. Application developers and DBAs should treat it as a black box; Oracle does not publish a supported call interface and the package may change across database patch levels without notice.

Key Procedures and Functions

The ETRM metadata for this object lists zero documented procedures or functions. This is consistent with its status as a private helper package body. No parameter lists are exposed or documented, and none should be inferred or constructed by the reader. The package is instantiated internally when the snapshot refresh subsystem performs low-water-mark bookkeeping during group refresh operations.

What is documented is its dependency graph, which reveals the interfaces it leverages rather than exposes:

DBMS_IREFRESH — a sibling internal refresh package the body depends on for the actual refresh execution logic.
DBMS_STANDARD and STANDARD — the standard PL/SQL runtime units.
DBMS_SYS_ERROR — the internal error-raising and messaging facility, indicating the package emits and propagates database exceptions.

The absence of documented callable units confirms that DBMS_REFRESH_EXP_LWM is invoked only by Oracle's own refresh code paths, not by application code.

Tables Accessed

The ETRM dependency listing records two data dictionary tables accessed by this package body:

RGROUP$ — the base dictionary table storing refresh group definitions, including membership of materialized views, refresh intervals, and next-refresh scheduling information. DBMS_REFRESH_EXP_LWM reads and maintains rows here as part of group-level refresh coordination.
DEF$_PUSHED_TRANSACTIONS — the deferred transaction queue table used by the replication engine to track pushed transactions. Access to this table indicates the package participates in determining which deferred transactions have been propagated, a prerequisite for deciding when a refresh's low-water mark can safely advance.

Both tables are owned by SYS/SYSTEM and are never accessed directly by EBS application code. Through its dependency on table references, the package ties the EBS refresh workflow into the underlying Oracle replication engine.

Usage Notes

DBMS_REFRESH_EXP_LWM is never invoked by Oracle Forms, concurrent programs, or EBS application logic. The ETRM metadata explicitly states that SYS.DBMS_REFRESH_EXP_LWM "is not referenced by any database object," and zero other packages reference it. It is consequently a leaf dependency, called only from within the compiled body of the refresh subsystem itself.

Its practical significance in EBS 12.1.1 and 12.2.2 is limited to troubleshooting. DBAs who observe refresh group failures, stale summary data, or errors originating in DBMS_REFRESH may find this object named in the dependency chain of a related internal package. Direct execution is neither supported nor meaningful, and customizations should never call it. Refresh groups used by EBS modules should be managed exclusively through the supported DBMS_REFRESH API or, where available, Oracle Enterprise Manager's refresh group console. Any modification to this SYS-owned body risks invalidating the replication stack and is unsupported by Oracle.

PACKAGE BODY: SYS.DBMS_REFRESH_EXP_LWM 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_REFRESH_EXP_LWM, status:VALID,
PACKAGE: SYS.DBMS_REFRESH_EXP_LWM 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_REFRESH_EXP_LWM, status:VALID,
PACKAGE BODY: SYS.DBMS_REFRESH_EXP_LWM 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_REFRESH_EXP_LWM, status:VALID,
PACKAGE: SYS.DBMS_REFRESH_EXP_LWM 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_REFRESH_EXP_LWM, status:VALID,
PACKAGE: SYS.DBMS_REFRESH_EXP_LWM 12.1.1
PACKAGE: SYS.DBMS_IREFRESH 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_IREFRESH, status:VALID,
PACKAGE: SYS.DBMS_REFRESH_EXP_LWM 12.2.2
PACKAGE BODY: SYS.DBMS_REFRESH_EXP_LWM 12.2.2
PACKAGE BODY: SYS.DBMS_REFRESH_EXP_LWM 12.1.1
PACKAGE: SYS.DBMS_IREFRESH 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_IREFRESH, status:VALID,
TABLE: SYSTEM.DEF$_PUSHED_TRANSACTIONS 12.2.2
owner:SYSTEM, object_type:TABLE, object_name:DEF$_PUSHED_TRANSACTIONS, status:VALID,
TABLE: SYS.RGROUP$ 12.1.1
owner:SYS, object_type:TABLE, object_name:RGROUP$, status:VALID,
TABLE: SYS.RGROUP$ 12.2.2
owner:SYS, object_type:TABLE, object_name:RGROUP$, status:VALID,
TABLE: SYSTEM.DEF$_PUSHED_TRANSACTIONS 12.1.1
owner:SYSTEM, object_type:TABLE, object_name:DEF$_PUSHED_TRANSACTIONS, status:VALID,
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
12.1.1 DBA Data 12.1.1
PACKAGE: SYS.DBMS_SYS_ERROR 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SYS_ERROR, status:VALID,
PACKAGE: SYS.DBMS_SYS_ERROR 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SYS_ERROR, status:VALID,
SYS.DBMS_REFRESH_EXP_LWM dependencies on DBMS_REFRESH_EXP_LWM 12.2.2
SYS.DBMS_REFRESH_EXP_LWM dependencies on DBMS_REFRESH_EXP_LWM 12.1.1
PACKAGE: SYS.DBMS_STANDARD 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_STANDARD, status:VALID,
PACKAGE: SYS.DBMS_STANDARD 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_STANDARD, status:VALID,
PACKAGE: SYS.STANDARD 12.1.1
owner:SYS, object_type:PACKAGE, object_name:STANDARD, status:VALID,