Search Results sy_purge_log_pk

Overview

The SY_PURG_LOG table is a core data object within the Oracle Process Manufacturing (OPM) module of Oracle E-Business Suite (EBS), specifically under the GMA (Process Manufacturing Systems) product family. Its primary role is to serve as a detailed audit and statistics log for purge and archive operations executed within the OPM environment. Each time a system administrator or a scheduled process runs a purge job to remove obsolete transactional or master data, this table captures granular, table-by-table execution metrics. This logging is critical for compliance, performance analysis, and verifying the success and scope of data lifecycle management activities in a manufacturing context.

Key Information Stored

The table records statistical outcomes for each database table processed during a purge run. While the full column list is not detailed in the provided metadata, the structure is defined by its primary key and foreign key relationships. The essential columns include:

• PURGE_ID: A foreign key linking the log entry to a master purge run record in the SY_PURG_MST table. This identifies the specific purge session.
• TABLE_NAME: The name of the specific OPM table (e.g., a transaction or master data table) that was targeted by the purge operation for that session.

Based on its described purpose, the table likely also contains quantitative columns such as the number of rows selected for purge, the number of rows successfully purged or archived, start and end timestamps for purging that specific table, and potentially a status or error message field. The composite primary key (SY_PURGE_LOG_PK) on PURGE_ID and TABLE_NAME ensures a unique record for each table processed within a given purge job.

Common Use Cases and Queries

The primary use case is post-purge analysis and reporting. Database administrators and functional consultants query this table to generate purge audit reports, troubleshoot failed jobs, and understand data volume impacts. A common query involves joining to the master purge table to get a complete summary of a job.

Sample Query: Summary of a Specific Purge Run
SELECT l.table_name, l.rows_processed, l.rows_deleted, l.start_time, l.end_time
FROM gma.sy_purg_log l, gma.sy_purg_mst m
WHERE l.purge_id = m.purge_id
AND m.purge_request_id = '12345'
ORDER BY l.table_name;
This pattern helps verify which tables were cleaned and the volume of data removed. Another typical report aggregates historical purge statistics by table to track data growth and purge effectiveness over time.

Related Objects

The SY_PURG_LOG table has a direct and documented foreign key relationship with the SY_PURG_MST table, which serves as the purge job header. The relationship is defined as follows:

• Foreign Key From: SY_PURG_LOG
• Foreign Key To (Parent Table): SY_PURG_MST
• Join Column: SY_PURG_LOG.PURGE_ID references SY_PURG_MST.PURGE_ID

This relationship is fundamental; every detailed log entry in SY_PURG_LOG must correspond to a master control record in SY_PURG_MST. The SY_PURG_MST table typically stores high-level job information such as the purge request ID, program name, submission time, overall status, and parameters used. To get a complete picture of any purge operation, these two tables must be queried together.