SQL Developer Database Diff: Architecture Note for Schema Comparison

Requirements

Developers and DBAs need a way to see what schema objects differ between two Oracle databases without touching data. The comparison must support version‑control workflows and pre‑deployment validation while remaining read‑only.

Smallest Suitable Design

The feature opens two read‑only JDBC connections, queries Oracle data‑dictionary views (e.g., ALL_TABLES, ALL_VIEWS, ALL_PROCEDURES) for metadata, runs an in‑memory diff engine that builds a hierarchical tree of object types, and returns a report. No persistent storage or server component is required.

Trust and Data Boundaries

Only SELECT statements are issued against dictionary views; no DDL or DML is ever executed. This keeps the operation inside the reader’s privilege boundary and prevents accidental writes to either database.

Operational Checks

• Connection validation – the tool verifies each JDBC URL can be opened.
• Privilege verification – it attempts a simple SELECT on a dictionary view; failure is reported as insufficient privileges.
• Timeout handling – network stalls abort after a configurable period.
• Post‑diff summary – shows counts of added, modified, and removed objects for quick review.

Failure Modes

• Missing privileges cause certain object types to be invisible, leading to false‑negative diffs.
• Very large schemas (thousands of objects) can exhaust heap memory or exceed time limits.
• Network interruptions terminate the comparison abruptly.
• Some object types (e.g., Oracle Text indexes, JSON indexes) may be omitted if the JDBC driver version does not expose them through the dictionary views.

Conditions That Would Change the Design

If bidirectional synchronization, heterogeneous database support (MySQL, PostgreSQL), or fully automated CLI‑driven runs become necessary, the design would need:

• A server‑side diff service with persistent storage for baselines.
• Write capabilities to apply generated DDL.
• Additional adapters for non‑Oracle metadata sources.

Example Workflow

1. Open SQL Developer and choose Tools → Database Diff. 2. In the wizard, create or select a source connection (e.g., jdbc:oracle:thin:@//prod-host:1521/PROD) and a target connection (e.g., jdbc:oracle:thin:@//test-host:1521/TEST). 3. Accept the default object types (tables, views, indexes, procedures, packages, triggers) or deselect packages to see how the filter changes the report. 4. Click Next and start the comparison. 5. The hierarchical report appears; each node shows the status (Added, Modified, Removed) and a tooltip with the object name. 6. To verify the read‑only nature, enable SQL tracing in the database (ALTER SESSION SET SQL_TRACE = TRUE;) before running the diff and check the trace file – only SELECT statements on dictionary views should appear.

Limitations and Practical Checks

Large schemas may trigger OutOfMemoryError. Increase SQL Developer’s heap via AddVMOption -Xmx2g in the ide.conf file, or reduce the object type list (e.g., exclude indexes) to lower memory usage. To confirm that newer features like invisible columns are visible, ensure the JDBC driver version is at least 19.3; older drivers may omit those columns from ALL_TAB_COLS, causing them to be missed in the diff. Finally, remember that the tool only compares metadata. For data‑level differences (row counts, column values) you must run a separate data comparison technique such as MINUS queries or a dedicated data‑diff utility.