如何在Oracle数据库中实现主表与归档表的DDL变更同步？

Hey there, let's tackle this Oracle DDL sync problem between your main table (A.T1_TAB) and archive table (B.T1_TAB_ARCH). I've got a couple of proven solutions depending on your real-time needs:

方案一：实时DDL同步触发器（推荐用于实时需求） #

This is the most straightforward approach if you need DDL changes to sync immediately. We'll create a schema-level DDL trigger that captures ALTER TABLE operations on A.T1_TAB, tweaks the DDL to target the archive table, and executes it.

First, make sure the user creating the trigger has the right permissions:

• ADMINISTER DATABASE TRIGGER to create schema-level triggers
• ALTER ANY TABLE to modify B.T1_TAB_ARCH
• SELECT ON V$SQL and SELECT ON V$SESSION to capture the executed DDL

Here's the trigger code:

CREATE OR REPLACE TRIGGER SYNC_T1_ARCH_DDL
AFTER ALTER ON A.SCHEMA
DECLARE
    v_ddl_stmt VARCHAR2(4000);
    v_new_stmt VARCHAR2(4000);
BEGIN
    -- Grab the current DDL statement being executed
    SELECT SQL_TEXT 
    INTO v_ddl_stmt 
    FROM v$sql 
    WHERE SQL_ID = (SELECT SQL_ID FROM v$session WHERE AUDIT_SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID'));
    
    -- Only process ALTER TABLE actions for T1_TAB
    IF INSTR(UPPER(v_ddl_stmt), 'ALTER TABLE A.T1_TAB') > 0 THEN
        -- Replace the main table name with the archive table name
        v_new_stmt := REPLACE(UPPER(v_ddl_stmt), 'ALTER TABLE A.T1_TAB', 'ALTER TABLE B.T1_TAB_ARCH');
        -- Run the synced DDL
        EXECUTE IMMEDIATE v_new_stmt;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- Critical: Log errors instead of failing the original DDL
        -- First, create a error log table if you don't have one:
        -- CREATE TABLE DDL_SYNC_ERRORS (ERROR_DATE DATE, ERROR_MSG VARCHAR2(4000), ORIGINAL_DDL VARCHAR2(4000), TARGET_DDL VARCHAR2(4000));
        INSERT INTO DDL_SYNC_ERRORS (ERROR_DATE, ERROR_MSG, ORIGINAL_DDL, TARGET_DDL)
        VALUES (SYSDATE, SQLERRM, v_ddl_stmt, v_new_stmt);
        COMMIT;
END;
/

Key Notes: #

• Error Handling: Never skip the exception block! If the trigger fails without handling it, the original ALTER TABLE on A.T1_TAB will roll back too. Logging errors lets you fix sync issues without blocking main table operations.
• Filtering: The trigger only targets ALTER TABLE on your specific table. You can adjust the condition to include other DDL types (like ADD CONSTRAINT) if needed.
• Case Sensitivity: Using UPPER() ensures we match regardless of how the DDL is written (e.g., alter table a.t1_tab vs ALTER TABLE A.T1_TAB).

方案二：定期结构同步（适合非实时场景） #

If you don't need instant sync (e.g., nightly updates are enough), a scheduled stored procedure that compares table structures and syncs differences works great. This avoids any impact on main table DDL operations.

First, create the sync procedure:

CREATE OR REPLACE PROCEDURE SYNC_T1_ARCH_STRUCTURE
IS
    v_diff_stmt VARCHAR2(4000);
BEGIN
    -- Compare columns between main and archive table, sync missing columns
    FOR col IN (
        SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
        FROM ALL_TAB_COLUMNS
        WHERE OWNER = 'A' AND TABLE_NAME = 'T1_TAB'
        MINUS
        SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
        FROM ALL_TAB_COLUMNS
        WHERE OWNER = 'B' AND TABLE_NAME = 'T1_TAB_ARCH'
    ) LOOP
        -- Generate ALTER TABLE statement to add the missing column
        v_diff_stmt := 'ALTER TABLE B.T1_TAB_ARCH ADD (' || col.COLUMN_NAME || ' ' || col.DATA_TYPE || 
                       CASE 
                           WHEN col.DATA_TYPE IN ('VARCHAR2', 'CHAR') THEN '(' || col.DATA_LENGTH || ')'
                           WHEN col.DATA_TYPE = 'NUMBER' AND col.DATA_PRECISION IS NOT NULL THEN '(' || col.DATA_PRECISION || ',' || col.DATA_SCALE || ')'
                           ELSE '' 
                       END || ')';
        EXECUTE IMMEDIATE v_diff_stmt;
    END LOOP;

    -- Optional: Add logic to sync constraints, indexes, or other table properties here
END;
/

Then schedule it with DBMS_SCHEDULER to run daily (e.g., at 2 AM):

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'SYNC_T1_ARCH_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'SYNC_T1_ARCH_STRUCTURE',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY;BYHOUR=2',
        enabled         => TRUE,
        comments        => 'Sync structure between A.T1_TAB and B.T1_TAB_ARCH'
    );
END;
/

Pros & Cons: #

• ✅ No impact on main table DDL operations (sync failures don't block users)
• ✅ Easy to extend to sync other table elements (constraints, indexes)
• ❌ Not real-time; changes will only sync on the schedule
• ❌ Needs extra logic to handle dropped columns or modified data types (adjust the MINUS query or add additional checks)

方案三：Oracle GoldenGate（企业级多表/跨库场景） #

If you're dealing with multiple tables, cross-database sync, or need enterprise-grade reliability, Oracle GoldenGate is the way to go. It's designed for real-time data and DDL replication, and you won't have to maintain custom triggers or procedures.

You'll need to:

1. Deploy GoldenGate agents on your source and target databases
2. Configure a replication group that includes DDL capture for A.T1_TAB
3. Map the source table to B.T1_TAB_ARCH in the replication configuration

This is a heavier setup but worth it for large-scale or complex sync requirements.

Final Recommendation: #

• Use 方案一 if you need real-time sync for a single table
• Use 方案二 if real-time isn't critical and you want a low-impact solution
• Use 方案三 for enterprise-level, multi-table, or cross-database scenarios

内容的提问来源于stack exchange，提问作者Kapil