How to Copy an Oracle Table Between Two Databases: Step-by-Step Guide

Very often, there is a need to test a scenario that runs in production. In such cases, we can use a lower environment, if available. Or try to replicate it on our own local machine. To do this, we must prepare everything as closely as possible to the original environment. If you’re using an Oracle database, you would likely want to copy an Oracle table between databases to prepare test data on your local database. Here is one solution that can help you quickly set up a local copy of production data.

In this tutorial, we will use a Docker image to set up a local Oracle database and connect from inside the Docker container to an external database using the HAProxy tool.

In this tutorial, we will use Docker image as a local Oracle database. Next, we will connect from Docker container to an external database using HAProxy tool.

Create a database link

The first step is to create a local link on your local database. Next we will use it to connect to an external one.

A database link is a schema object in one database that enables you to access objects on another database.

create public database link 
  LINK_TO_EXTERNAL_DB
connect to 
  username
identified by 
  password
using 'net_service_name from tnsnames.ora'; -- or 'host.docker.internal:PORT/SERVICE_NAME'
COMMIT;

Next, we can verify if a link has been created by executing below command:

SELECT * FROM ALL_DB_LINKS;

If you would like to remove the newly added database link, execute the below command:

DROP PUBLIC DATABASE LINK  LINK_TO_EXTERNAL_DB;

Ok, that was easy. But what if you are using MacBook and Oracle as a docker container?

Expose oracle port as internal one

We will use HAProxy in order to connect to an external Oracle database using our local port.

Let’s create a file called haproxyOracleProd.cfg:

frontend oracleFront
  bind *:1522
  mode    tcp

  use_backend oracleBackend

backend oracleBackend
  mode    tcp
  server oracleNode externalOracleHostName:1521

Now we can start HAProxy with our configuration:

haproxy -f haproxyOracleProd.cfg

After that we can connect to an external oracle database using a local port:

sqlplus user/password@localhost:1522/SERVICE_NAME

Configure Oracle inside Docker

As pointed out in the first snippet, we can use 'host.docker.internal:PORT/SERVICE_NAME’ or the service name from the tnsnames.ora file used by our Oracle Docker instance. Furthermore, if you have mapped a volume for Oracle files on your local machine, you can easily edit this file, and all changes will be persisted between Docker restarts.

This is how I’m starting my oracle docker container:

docker run --name oracle19 --shm-size="2g" -v /Users/{MyName}/oradata19c:/opt/oracle/oradata --restart=always -p 1521:1521 -p 5500:5500 -e ORACLE_SID={MY_SID} -e ORACLE_PDB={MY_PDB} -e ORACLE_PWD={MY_PASSWORD} -d oracle/database:19.3.0-ee

Now, as you see I have all important oracle files in my local machine under: /Users/{MyName}/oradata19c

We can edit file: /Users/{MyName}/oradata19c/dbconfig/{DB_NAME}/tnsnames.ora in order to add new connection entry:

EXTERNAL_DB_SERVICE =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.docker.internal)(PORT = 1522))
    (CONNECT_DATA = (SERVICE_NAME = YOUR_SERVICE_NAME_FROM_EXTERNAL_DB)(SERVER = DEDICATED))
  )

After that our SQL command to create DB links could look like:

create public database link 
  LINK_TO_EXTERNAL_DB
connect to 
  username
identified by 
  password
using 'EXTERNAL_DB_SERVICE';

Example usage

Now the coolest part. We can utilize our DB link in a very convenient way.

Using DB link

We can query an external database using DB link, for example:

SELECT count(*) FROM TABLE_NAME@LINK_TO_EXTERNAL_DB;

Copy table DDL using DB link:

We can clone the structure of a table from an external database into a local one with this very simple command:

create table LOCAL_TABLE AS (select * from EXTERNAL_TABLE@LINK_TO_EXTERNAL_DB where 1=2 );

Copy table content from external Oracle DB into Local DB using DB Link:

Now we can copy in a very simple way entire data or part of table data depending on our needs.

INSERT into LOCAL_TABLE select * from EXTERNAL_TABLE@LINK_TO_EXTERNAL_DB;

Important limitations when you copy Oracle table between databases

All of the above seems like a clever and ideal solution. Therefore, it looks like easy task to clone database tables from one DB to another. However, there are some flaws. Several DB elements are not copied along with your table, such as triggers, indexes, and sequences. The most frustrating and unexpected issues (at least for me) are constraints and default values.

I was very surprised after copying a lot of tables that are used by a very large codebase. When I ran the code, some exceptions started to occur. This was due to a lack of default values. This is something that needs to be taken into consideration if you plan to use the method described in the post here.

Summary

At first glance, copying a table using a SELECT statement seems like a good solution. However, I would not recommend it as the default approach for copying DDL from one table to another. The worst part is that if you don’t review the newly created table and compare it to the original version, you’ll only discover discrepancies at a later stage, when you might not expect anything to be wrong with your database.