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.