Post cover image

June 11, 2026

Find Schema Differences Between 2 Oracle Databases and Migrate Them with DataPump

Managing large-scale Oracle databases often requires synchronizing environments — such as moving missing objects from a Production system…

M. Samet Gemici

3 min read

Managing large-scale Oracle databases often requires synchronizing environments — such as moving missing objects from a Production system to a Test environment. Finding schema differences and migrating them seamlessly is a critical DBA operation.

In this guide, we will walk through the entire process step-by-step: from network access checks and finding missing objects using the MINUS operator, to performing the migration using DataPump's most powerful parameters and network links.

Prerequisites (Before You Begin)

Ensure the database user performing these operations has the necessary privileges: CREATE DATABASE LINK, CREATE ANY DIRECTORY, and DATAPUMP_EXP_FULL_DATABASE / DATAPUMP_IMP_FULL_DATABASE.

-- Create private database links
GRANT CREATE DATABASE LINK TO MURATSAMET_GEMICI;

-- Create and manage Oracle directories
GRANT CREATE ANY DIRECTORY TO MURATSAMET_GEMICI;

-- Data Pump Export privileges
GRANT DATAPUMP_EXP_FULL_DATABASE TO MURATSAMET_GEMICI;

-- Data Pump Import privileges
GRANT DATAPUMP_IMP_FULL_DATABASE TO MURATSAMET_GEMICI;

Step 1: Network and Access Controls

For a successful DataPump operation across two servers, they must be able to communicate flawlessly. Before writing any code, verify the following:

  • Port Permissions (Firewall/ACL): The database listener port (usually 1521) must be open from the source server to the target server.

First, add the target database entry to the tnsnames.ora file. Example:

XTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exax-scan)(PORT = 14X))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XTEST)
    )
  )

Then test the connectivity using Oracle Net:

None

The source server can reach the target listener.

The listener port (1472) is accessible.

No firewall is blocking the connection.

The target service can be resolved through Oracle Net.

  • TNS Definitions: Ensure the target server's IP, port, and service name are correctly defined in your tnsnames.ora file.
  • Listener Status: The listener on the target database must be up and running, listening to the correct service_name.
SHOW PARAMETER local_listener;
None 
/* Connect to the target CDB and execute: */

SELECT name,
       network_name,
       pdb
FROM gv$services
WHERE pdb = 'XDB';

/* This confirms that the XDB service is registered and available for client connections. */
  • IP Accessibility: Confirm that the servers can ping each other and that there are no network-level blocks (security lists, iptables, hardware firewalls).

We need to create a DB_LINK in the source database to establish a bridge to the target database. For security reasons, we avoid creating a PUBLIC db link.

CREATE DATABASE LINK MURATSAMET_DB_LINK
CONNECT TO MURATSAMET_GEMICI
IDENTIFIED BY <password>
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=exaX-scan)(PORT=1472))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XTEST)))';

Troubleshooting Tip: If you test the DB Link via Toad and encounter an ORA-12154 error, while tnsping on the server is successful, the reason is clear: Toad uses the tnsnames.ora file on your local machine. Ensure your local TNS addresses match the server's.

Step 3: Finding Schema Differences with MINUS

To find the missing objects between the source and target schemas, you can use the following script. You can add or remove object types (TABLE, VIEW, PROCEDURE, etc.) based on your needs.

SELECT object_name, object_type
FROM dba_objects
WHERE owner = 'X'
  AND object_type IN ('TABLE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'SYNONYM')
MINUS
SELECT object_name, object_type
FROM dba_objects@MURATSAMET_DB_LINK
WHERE owner = 'X_HIST'
  AND object_type IN ('TABLE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'SYNONYM');

Step 4: The Classic Method (Export & Import via Directory)

Create a DataPump directory on the source DB to specify where the .dmp and .log files will be stored, and grant the necessary permissions.

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/tmp';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO MURATSAMET_GEMICI;

Create a parameter file (exp_objects_selected.par) and list the missing objects you found via the MINUS query:

vi exp_objects_selected.par

userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=x_selected_01.dmp
logfile=x_selected_exp.log
schemas=SOURCE_SCHEMA
compression=all
reuse_dumpfiles=yes
include=TABLE:"IN ('TABLE_NAME1', 'TABLE_NAME2')"
include=VIEW:"IN ('VIEW_NAME1')"

Run the Export command on the Source DB server:

expdp parfile=exp_objects_selected.par

Once the export is complete, transfer the .dmp file to the target server (e.g., via scp) and run the Import command on the Target DB (running as PDB):

impdp muratsamet_gemici@XTESTDB DIRECTORY=DATA_PUMP_DIR dumpfile=x_selected_01.dmp REMAP_SCHEMA=SOURCE_SCHEMA:NEW_SCHEMA cluster=N LOGFILE=x_selected_imp.log

Lifesaver DataPump Parameters You Must Know

Depending on the operation, these parameters will save you from major headaches during the import phase:

1. TABLE_EXISTS_ACTION

What happens if the table you are migrating already exists in the target database? This parameter is a crisis solver.

  • SKIP: Skips the table and moves to the next object (Default).
  • APPEND: Writes only new rows into the existing table.
  • TRUNCATE: Empties the target table and inserts the new data.
  • REPLACE: Completely drops the target table, recreates it, and inserts the data. (This is usually the best choice if you are fixing structural schema differences).

2. CONTENT

Are we moving just the missing "schema" objects, or the data inside them too? By default, DataPump brings millions of rows inside the tables. If your goal is only to synchronize the database architecture (tables, views, procedures), you must add CONTENT=METADATA_ONLY to your parameter file.

3. REMAP_TABLESPACE

Warning: Before importing, check if the tablespaces of the exported objects exist on the target DB. If they don't, the import will fail. Solution: You don't need to create dummy tablespaces on the target. Simply use the REMAP_TABLESPACE=SOURCE_TS:TARGET_TS parameter in your impdp command to seamlessly map them.