Search Results refresh_metadata_tables




Overview
Explore in AI Chat

Overview

EDW_METADATA_REFRESH is an Oracle E-Business Suite PL/SQL package owned by the APPS schema and classified under the ETRM taxonomy as an OTHER API. It is part of the Enterprise Data Warehouse (EDW) infrastructure used to stage and publish metadata about the EBS database itself for downstream analytical, reporting, and Oracle Warehouse Builder (OWB) purposes. The package coordinates a metadata refresh cycle: it extracts information about database objects, populates private (staging) tables, refreshes materialized views, gathers optimizer statistics, and writes an activity log so that administrators can audit each run. It is declared AUTHID CURRENT_USER, meaning its unqualified references resolve against the invoker's schema. The header comment indicates the package has been stable since 2003 (version 115.4), reflecting its role as long-lived infrastructure rather than as an application-facing business API. No business transaction validation, workflow, or user-interface logic is performed here; the package is purely a technical utility for populating and maintaining the metadata repository that supports the EDW and BI layers.

Key Procedures and Functions

The package exposes ten documented program units.

  • REFRESH_METADATA_TABLES — the primary entry point. Accepts standard concurrent-program OUT parameters for error buffer and return code, and orchestrates the full metadata refresh sequence.
  • GET_DB_USER — returns the database user associated with a supplied product, used to resolve product ownership during metadata extraction.
  • LOG — writes a message to the package log, using the G_STATUS_MESSAGE variable as the current status holder.
  • GET_TIME — returns a formatted timestamp for log entries.
  • DROP_TABLE — removes a named staging table as part of cleanup between refresh runs.
  • POPULATE_PVT_TABLES — the unit the user searched for. It returns a Boolean success indicator and is responsible for loading the package's private staging tables with the metadata harvested from the data dictionary.
  • ANALYZE_ALL — gathers optimizer statistics across the metadata tables so that subsequent queries against them perform predictably.
  • TRUNCATE_ALL — returns a Boolean and clears the staging tables prior to, or in place of, a full reload.
  • OPEN_LOG_FILE — initializes the log file or log target used by LOG and GET_TIME.
  • REFRESH_OWB_MV — returns a Boolean and refreshes the materialized views consumed by Oracle Warehouse Builder.

Tables Accessed

Two dictionary objects are documented as referenced through APPS synonyms. DBMS_SNAPSHOT is used to perform the materialized view refresh work associated with REFRESH_OWB_MV, exploiting the standard replication refresh interface rather than issuing manual DML. USER_OBJECTS supplies the object inventory — names, types, and status — that POPULATE_PVT_TABLES extracts to build the staging content. Because the package is AUTHID CURRENT_USER, these dictionary queries are evaluated in the context of the invoking schema. The package's own private staging tables (the "pvt" tables named in POPULATE_PVT_TABLES) serve as the write targets for the harvested metadata and are the subject of the TRUNCATE_ALL, ANALYZE_ALL, and DROP_TABLE utilities.

Usage Notes

EDW_METADATA_REFRESH is normally invoked through the REFRESH_METADATA_TABLES concurrent program, whose Errbuf and Retcode signatures match the standard EBS concurrent manager contract. It is not exposed through a Forms interface or a public business API. One other package is documented as referencing it, indicating it participates in a small dependency chain within the EDW schema rather than in a broad call graph. Administrators generally run it on a scheduled basis — commonly during a low-activity window — after structural changes to the EBS database or when OWB metadata appears stale. Custom code that calls the package should honor the Boolean return values from POPULATE_PVT_TABLES, TRUNCATE_ALL, and REFRESH_OWB_MV and should treat a FALSE result as a failed refresh, inspecting the log written by LOG and GET_TIME before retrying.