Database Diff incremental sync blocking application during migration

Answer to the Core Question

Oracle SQL Developer’s Database Diff cannot be toggled to produce a truly lock‑free incremental DDL script. It always emits standard DDL that, when executed, will acquire an Access Exclusive lock on the target objects. However, you can mitigate the impact on a live application by applying the generated DDL in a staged, online‑friendly manner.

Why the DDL Blocks

• DDL such as ALTER TABLE, DROP COLUMN, or CREATE INDEX requires an exclusive lock on the table or index object.
• During the lock period, all concurrent DML sessions are blocked, which can appear as application downtime.
• Long‑running transactions held by the application can delay the DDL from acquiring the lock, extending the blocking window.

Recommended Workflow for an Online Migration

1. Generate the Diff – Use Database Diff to produce the full DDL script. Keep the script in a version‑controlled file for review.
2. Separate Additive vs. Modifying Changes – Split the script into two parts:
   • Part A: Additive changes (new tables, columns, indexes). These usually do not block existing DML.
   • Part B: Modifying or destructive changes (alter column type, drop column, drop constraint).
3. Apply Additive Changes First – Execute Part A during a low‑traffic window or in a maintenance window. Since these changes are non‑blocking, the application can continue to run.
4. Use Online Redefinition for Critical Tables – For tables that require column changes or constraint modifications, wrap the change in a DBMS_REDEFINITION session:

   DECLARE
     l_redef_handle NUMBER;
   BEGIN
     DBMS_REDEFINITION.START_REDEF('SCHEMA', 'TABLE_NAME', l_redef_handle);
     DBMS_REDEFINITION.ALTER_TABLE(l_redef_handle, 'ALTER TABLE TABLE_NAME ADD (new_col NUMBER)');
     DBMS_REDEFINITION.FINISH_REDEF(l_redef_handle);
   END;
   

   This technique keeps the original table available for DML while the new structure is built in the background.
5. Create Indexes Online – When Part B includes index creation, use the ONLINE keyword (Enterprise Edition only):

   CREATE INDEX idx_name ON table_name(col1) ONLINE;

6. Apply Modifying Changes During a Planned Window – Execute Part B after the online redefinition and online index creation are complete. If the application load is high, schedule a brief maintenance window and monitor active sessions via V$SESSION and V$LOCKED_OBJECT.
7. Post‑Migration Verification – Verify that all new objects are present and that application sessions resume normal operation. Run a quick consistency check against the staging environment copy.

Monitoring and Safe Execution

• Before running any DDL, set a short lock timeout to avoid long waits: ALTER SESSION SET DDL_LOCK_TIMEOUT = 60;
• Use SELECT * FROM v$session WHERE blocking_session IS NOT NULL; to spot blocking sessions and decide whether to wait or kill them.
• Always test the full script on a cloned production dataset to measure lock duration and confirm that the application can tolerate the brief pause.

Missing Diagnostic Detail

To refine the recommendation, please confirm whether the target database is Oracle Enterprise Edition. Online index creation and DBMS_REDEFINITION are Enterprise‑only features.