How to copy a table between two databases in Oracle

Very often there is a need to test a scenario that executes on production. We can use for such task lower environment (if we have any) or we can try to mimic this on our own local machine. To do this we have to prepare everything as close to the original environment as possible. If you are using the Oracle database you would like to prepare test data on your local database. Here is one solution that can help you in preparing a local copy of production data fast.
In this tutorial, we will use Docker image as a local Oracle database and connect from inside Docker container to an external database using HAProxy tool.

In this tutorial, we will use Docker image as a local Oracle database and connect from inside 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 that will connect you to the 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 in the first snipped we can use 'host.docker.internal:PORT/SERVICE_NAME’ or service name from tnsnames.ora that is used by our oracle docker instance. If you have mapped 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:

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

All above seems to be the very clever and ideal solution to very easily clone database tables from one DB to another. But as always there are some flaws. There are a bunch of DB elements that are not copied along with your table like: triggers, indices, sequences but the most frustrating and not expected (at least by me) were constraints and default value.

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

Summary

At first glance copying a table with the usage of select from seems to be a good solution but I would not recommend it to use as the default approach for copying DDL from one table to another. The worst is that if you don’t look at the newly created table and don’t compare it with the original version you will know about discrepancies at a later stage when you don’t expect that sth is wrong with your database.