Results for “sy_purg_mst”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

SY_PURG_MST is a GMA (Process Manufacturing Systems) table that stores data describing every archive or purge run executed against Oracle Process Manufacturing data. In Oracle EBS 12.1.1 and 12.2.2, it functions as the master control and audit record for the OPM archive/purge utilities, capturing the parameters used, the volumes of data affected, and the timing statistics of each job. Because it retains a row per run rather than per transaction, it provides the historical tracking that administrators rely on to confirm which archives and purges were performed, when, and with what outcome.

Under the heuristic Data Vault classification supplied in the metadata, SY_PURG_MST is hub-leaning. This is a modeling suggestion: PURGE_ID acts as the stable business key around which purge execution identifiers are registered, while the descriptive statistics attached to each run behave satellite-like. Related dependent tables (errors, logs, criteria) then reference the hub through PURGE_ID.

Key Information Stored

The table contains 30 documented columns. The most significant are:

• PURGE_ID — the surrogate primary key, enforced by the SY_PURG_MST_PK unique index, uniquely identifying each archive/purge run.
• PURGE_TYPE — the category of purge performed; foreign key to SY_PURG_DEF, linking the run to its definition.
• STATUS and PURGE_STATUS — the outcome/state of the run.
• PURGE_COMMENT — free-text annotation supplied by the administrator.
• ROWS_ARCHIVED and ROWS_DELETED — counts of records archived versus deleted.
• ARCHIVE_START_TIME, ARCHIVE_ELAPSED_TIME, PURGE_START_TIME, PURGE_ELAPSED_TIME — the timing metrics for the archive and purge phases.
• SELECTION_ELAPSED_TIME and COPY_ELAPSED_TIME — phase-level performance measurements.
• TRANS_CNT, ROWS_PER_SECOND, COPY_ROWS_PER_SECOND — throughput figures for tuning.
• TEXT_CODE — foreign key to SY_TEXT_HDR, associating run-level descriptive text.
• ARCHIVE_TABLE_COUNT, OBJECT_OWNER — scope and ownership of the archived objects.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — standard EBS auditing columns.

The only documented unique index is SY_PURG_MST_PK on PURGE_ID, making it the sole business-key candidate recorded in the ETRM metadata.

Common Use Cases and Queries

Typical uses include auditing purge history, verifying that a scheduled archive completed successfully, and analyzing runtime performance. A representative query retrieving recent runs with their type:

• SELECT m.PURGE_ID, d.DESCRIPTION, m.STATUS, m.ROWS_ARCHIVED, m.ROWS_DELETED, m.PURGE_ELAPSED_TIME FROM GMA.SY_PURG_MST m, GMA.SY_PURG_DEF d WHERE m.PURGE_TYPE = d.PURGE_TYPE ORDER BY m.CREATION_DATE DESC;
• To find failed or incomplete runs: SELECT PURGE_ID, STATUS, PURGE_COMMENT FROM GMA.SY_PURG_MST WHERE STATUS <> 'COMPLETE';
• To correlate a run with its detailed log or error records: SELECT * FROM GMA.SY_PURG_LOG WHERE PURGE_ID = :purge_id;

Reporting typically aggregates ROWS_DELETED and ROWS_ARCHIVED by PURGE_TYPE over a period to monitor data growth and purge effectiveness.

Related Objects

• SY_PURG_DEF — referenced by SY_PURG_MST.PURGE_TYPE; defines the purge types.
• SY_TEXT_HDR — referenced by SY_PURG_MST.TEXT_CODE; holds associated text.
• SY_PURG_ERR — child table keyed by PURGE_ID, storing errors per run.
• SY_PURG_LOG — child table keyed by PURGE_ID, storing execution logs.
• SY_PURG_MST_CRIT — child table keyed by PURGE_ID, storing selection criteria for the run.