Moving data between Oracle Instances with Linked Server

We are officially one week away from the open of the general fishing season here in southeast Wisconsin. I am incredibly excited to get back on the water and try to catch some big fish. I haven’t yet decided where I’m headed for the opener, but I had great luck on the east side of Pewaukee Lake last year, so I’m thinking about heading back there.
In the meantime, there are a few opportunities to get fishing before the general opener. Last weekend I went up to the Wolf River near Fremont to try to catch some walleyes. The entire Winnebago system, which includes the Wolf and Fox rivers and several lakes includeing Winnebago, Butte de Mortes, Partridge, and Poygan are all open all year round. Additionally, the walleye and whitebass from the lake systems head up and down the rivers to spawn around this time of year, and the fishing can be nothing short of amazing. Unfortunately, we didn’t happen to be on at the right time and ended up catching no fish. It was cool to try it once, especially since the fishing is legendary, it gets rather crowded. We fished about 1/2 mile stretch of the Wolf River just north of Partridge lake and we must have been among at least 40 boats. I’m personally not a big fan of crowded fishing, but it wasn’t as bad as I thought it would be.


There are a few ways to move data between different instances of Oracle database. In this post I will demonstrate how to set up a database link and use that to transport data from one environment to the other. The reason I did this recently was to update a small subset of our Test database with data from Prod. We didn’t want to update an entire database, tablespace, or even table. We simply wanted to update a single column on one table in the Test environment with data from Prod. The first thing I did was to create a database link from the Test environment to Production.

CREATE DATABASE LINK myProdDB
CONNECT TO mySchema INDENTIFIED BY SchemaPW
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OracleProdDB.Domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myProdDB)
	  (UR = A)
    )
  )';

A list of database links can be found by querying the DBA_DB_LINKS view:

SELECT    *
FROM      DBA_DB_LINKS;

Next to physically move the data from the Prod to the Test instance, I create a new table with the data I want, using the data from Prod:

CREATE TABLE prod_Copied_Table AS
SELECT  *
FROM    mySchema.CopiedTable@myProdDB;

Immediately I drop my database link – I don’t need it anymore and don’t want anyone else to use it.

DROP DATABASE LINK myProdDB;

Now I have all the data under my user’s schema in Test. The last step is simply to update data I want changed:

UPDATE SCHEMA.TestTable test
SET test.myDate = (SELECT myDate
                      FROM prod_Copied_Table prod 
                      WHERE test.myCOMPANY_ID = prod.myCOMPANY_ID 
                           AND test.myVENDOR_ID = prod.myVENDOR_ID 
                           AND test.mySKU = prod.mySKU 
                           AND test.myVENDOR_PART_ID = prod.myVENDOR_PART_ID)
                      WHERE test.myDate = TO_DATE('2016-04-29','yyyy-mm-dd');
                     COMMIT;

Now my data migration is complete. I drop the unneeded table from my user and I’m finished.

DROP TABLE prod_Copied_Table;

I hope you have found this example of moving data from one instance to another helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s