Search Results dbms_refresh




Overview
Explore in AI Chat

Overview

SYS.DBMS_REFRESH is an Oracle-supplied PL/SQL package that provides the administrative infrastructure for managing refresh groups in a replicated or snapshot-based environment. Within Oracle E-Business Suite 12.1.1 and 12.2.2 the package is not part of the functional application layer but rather a database kernel utility exposed to the SYS schema, classified in the ETRM repository as OTHER. Its purpose is to create, modify, and destroy refresh groups — named collections of snapshot (materialized view) objects that are refreshed together to preserve read consistency across related tables.

In an EBS context, this capability matters because several reporting and data-warehouse style schemas depend on materialized views that must be synchronized as a single transactional unit. DBMS_REFRESH provides the grouping and scheduling logic that guarantees referential consistency between those dependent objects. The ETRM record shows the object status as VALID and confirms it is referenced by three other packages, underscoring its role as a foundational dependency rather than an application-facing API.

Key Procedures and Functions

The ETRM metadata documents twelve callable programs. The principal ones are:

  • MAKE — Creates a new refresh group and optionally enrolls an initial set of members. This is the primary entry point for establishing a scheduled refresh.
  • MAKE_REPAPI — A variant used in replication API contexts, performing the same group construction for environments managed by the replication subsystem.
  • ADD — Adds one or more snapshot objects to an existing refresh group.
  • SUBTRACT — Removes members from a refresh group without destroying the group itself.
  • DESTROY — Eliminates a refresh group once it is no longer required.
  • CHANGE — Alters the attributes of a refresh group, such as the refresh interval or next scheduled execution time.
  • REFRESH — Executes an immediate manual refresh of all members in a group, independent of the scheduled interval.
  • USER_EXPORT and USER_EXPORT_CHILD — Generate the DDL required to recreate refresh group definitions in another schema or database, supporting migration and cloning activities.

No parameter lists are asserted here, as the ETRM dependency record documents only program names and ownership.

Tables Accessed

The supplied ETRM extract does not enumerate tables accessed through APPS synonyms, which is expected because DBMS_REFRESH operates against SYS-owned dictionary objects rather than application tables. Internally it maintains the refresh group definition and membership metadata in the data dictionary (the SYS.RGROUP* and related refresh-group tables) and depends on the job scheduling infrastructure that ultimately resides in DBA_JOBS. The documented dependency list confirms references to SYS.DBMS_UTILITY and STANDARD, indicating that it leverages timestamp and utility routines while manipulating this dictionary state.

Usage Notes

In EBS 12.1.1 and 12.2.2, DBMS_REFRESH is typically invoked indirectly. The Application DBA or a DBA script calls it when configuring materialized views used for reporting, distributed queries, or data synchronization between EBS and external systems. It may be scheduled from a concurrent program wrapper or a database job so that refresh groups fire at intervals appropriate to the reporting cadence.

Because the package is owned by SYS and exposed through a PUBLIC synonym, custom code can call it directly, but doing so should be restricted to privileged administration. The three dependent packages (DBMS_ISNAPSHOT, DBMS_REPCAT_RGT_CUST2, and DBMS_REPCAT_SNA_UTL) demonstrate that Oracle's own replication components rely on it. Any modification to refresh group definitions should be performed during maintenance windows, and groups should be destroyed only after confirming that no downstream snapshots or scheduled jobs depend on them.