Oracle EBS: How to Find Application Tables with Supplemental Logging Enabled

When working with replication tools (like Fivetran HVR, Oracle GoldenGate, or Qlik Replicate) in an Oracle E-Business Suite (EBS) environment, Change Data Capture (CDC) requires Supplemental Logging to be enabled. This ensures that the Redo Log stream contains the necessary pre- and post-image column values for change tracking.

As DBAs, we often need to audit which EBS application tables are currently configured with supplemental log groups and inspect the exact columns being logged. Here are the SQL queries to identify, detail, and summarize supplemental logging across all EBS schemas.

---

1. List All EBS Tables with Supplemental Log Groups

This query identifies all EBS application schema tables that have supplemental logging enabled (both system-generated key log groups and user-defined CDC log groups):

SELECT lg.owner,
       lg.table_name,
       lg.log_group_name,
       lg.log_group_type,
       lg.always,
       lg.generated
FROM dba_log_groups lg
WHERE lg.owner IN (
    SELECT oracle_username 
    FROM fnd_oracle_userid
    UNION
    SELECT 'APPS' FROM dual
)
ORDER BY lg.owner, lg.table_name;

---

2. Detailed Column-Level Supplemental Logging Breakdown

To inspect the exact columns included within each supplemental log group (useful for identifying non-key logged columns added by replication software):

SELECT lg.owner,
       lg.table_name,
       lg.log_group_name,
       lg.log_group_type,
       lc.column_name,
       lc.position,
       lc.logging_property
FROM dba_log_groups lg
JOIN dba_log_group_columns lc
  ON  lg.owner = lc.owner
  AND lg.table_name = lc.table_name
  AND lg.log_group_name = lc.log_group_name
WHERE lg.owner IN (
    SELECT oracle_username 
    FROM fnd_oracle_userid
    UNION
    SELECT 'APPS' FROM dual
)
ORDER BY lg.owner, lg.table_name, lg.log_group_name, lc.position;

---

3. Aggregated Summary by EBS Schema / Module

To quickly gauge replication footprint across different functional modules (e.g., AP, AR, GL, INV, PO), run this aggregation query:

SELECT lg.owner AS ebs_schema,
       COUNT(DISTINCT lg.table_name) AS table_count,
       COUNT(lg.log_group_name) AS log_group_count
FROM dba_log_groups lg
WHERE lg.owner IN (
    SELECT oracle_username 
    FROM fnd_oracle_userid
    UNION
    SELECT 'APPS' FROM dual
)
GROUP BY lg.owner
ORDER BY table_count DESC;

---

Quick Reference: Understanding LOG_GROUP_TYPE Values

• PRIMARY KEY LOGGING: Logs all primary key columns when an update occurs.
• UNIQUE KEY LOGGING: Logs unique key columns when an update occurs.
• FOREIGN KEY LOGGING: Logs foreign key columns when an update occurs.
• USER LOG GROUP: Custom column-list log group explicitly created for replication tools (e.g., HVR or GoldenGate).
• ALL COLUMN LOGGING: Logs all table columns (often set during initial load or specific conflict resolution rules).