Search Results register_mview

Overview

SYS.DBMS_SNAPSHOT is an Oracle-supplied PL/SQL package that provides the original procedural interface for managing materialized views and snapshot refresh operations in the database. Although superseded in name and extended functionality by the DBMS_MVIEW package, DBMS_SNAPSHOT remains present and VALID in Oracle E-Business Suite environments running on 12.1.1 and 12.2.2, and it continues to act as the underlying implementation of several refresh routines. In the EBS context, the package supports the refresh, registration, and administration of materialized views used for reporting, data warehousing, and replication between environments, particularly where EBS schemas rely on summary and interface views that must be refreshed on schedule or on demand.

The ETRM metadata classifies DBMS_SNAPSHOT under the OTHER API classification within the SYS schema. It is referenced by six other packages, including DBMS_MVIEW, DBMS_IREFRESH, DBMS_REPCAT_SNA_UTL, DBMS_REPCAT_UNTRUSTED, DBMS_REPCAT_MIG_INTERNAL, and DBMS_SNAPSHOT_UTL, indicating its central role in Oracle's replication and refresh infrastructure. The metadata identifies 40 documented procedures and functions.

Key Procedures and Functions

The documented procedures correspond to three functional areas: refresh, registration, and diagnostics.

• Refresh routines: REFRESH, REFRESH_ALL, REFRESH_DEPENDENT, and REFRESH_ALL_MVIEWS perform refresh of a specified materialized view, all materialized views, or the dependent set of views affected by a source object. These are the most commonly invoked members in EBS.
• Registration routines: REGISTER_MVIEW and UNREGISTER_MVIEW register or deregister a materialized view with the master site, maintaining the metadata required for refresh coordination.
• Log and purge routines: PURGE_LOG, PURGE_DIRECT_LOAD_LOG, and PURGE_MVIEW_FROM_LOG remove obsolete entries from materialized view logs after successful refreshes.
• Dependency and diagnostic routines: GET_MV_DEPENDENCIES, EXPLAIN_MVIEW, EXPLAIN_REWRITE, EXPLAIN_REWRITE_SQLID, and ESTIMATE_MVIEW_SIZE return metadata used to assess refresh dependencies, query rewrite capability, and storage requirements.
• Session and maintenance controls: SET_I_AM_A_REFRESH, I_AM_A_REFRESH, BEGIN_TABLE_REORGANIZATION, END_TABLE_REORGANIZATION, PMARKER, and SET_UP control refresh-flag state and support online table reorganization.

Tables Accessed

The ETRM metadata lists no directly referenced tables via APPS synonyms, reflecting that DBMS_SNAPSHOT operates primarily against data dictionary objects owned by SYS. These include the materialized view metadata tables (SYS.MVIEW$ and related dictionary views such as DBA_MVIEWS, DBA_MVIEW_LOGS, and DBA_MVIEW_REFRESH_TIMES) that record refresh history, log state, and dependency information. The package reads and updates these dictionary structures to determine which views are eligible for refresh, when refresh last occurred, and which dependent objects must be processed together.

Usage Notes

In Oracle EBS 12.1.1 and 12.2.2, DBMS_SNAPSHOT is generally not called directly from standard EBS forms. It is invoked indirectly through DBMS_MVIEW, which Oracle documentation recommends for new development. EBS concurrent programs that refresh reporting materialized views, custom interfaces, and replication routines built on DBMS_MVIEW ultimately execute DBMS_SNAPSHOT code paths. Custom PL/SQL should prefer DBMS_MVIEW for forward compatibility, reserving direct use of DBMS_SNAPSHOT for legacy code already committed to that API. Scheduled refreshes are typically driven through the Oracle Database job scheduler or EBS concurrent managers, requiring the refresh routines and the associated materialized view logs to be granted appropriate privileges to the executing schema.