| December | ||||||
| « | 2009 | » | ||||
| 29 | 30 | 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 27 | 28 | 29 | 30 | 31 | ||
posted: 04 Dec 2009
Today I needed to establish a link between my dev Oracle instance and my live instance, so that I could update the tables in my development instance with a fresh copy of the live data. This db link used to exist but a few weeks back, the live database moved server and the legacy links were now broken. The usual syntax for creating such a link is:
CREATE DATABASE LINK "new_link_name"
CONNECT TO "remote_user_name" IDENTIFIED BY "remote_user_password"
USING 'service_name';
The 'service_name' value is taken from an entry in the tnsnames.ora file on the local server, however, I was working remotely and regardless, don't have file system access to the remote server to check out the tns file. So what to do. Well, it seems that any 'proper' descriptor can be used in the USING clause, so I figured, what if I copy the full test of the descriptor out of my local tns file and insert it, something like:
CREATE DATABASE LINK "new_link_name"
CONNECT TO "remote_user_name" IDENTIFIED BY "remote_user_password"
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521 )))(CONNECT_DATA=(SERVER=DEDICATED)(SID=sid_of_remote)))';
And sure enough it worked. The only hiccup I had was that our new live db is a 10g instance and no longer accepted the older service name style argument, instead insisting on a SID. My original attempt had the end of the descriptor as ...(SERVICE_NAME=service_name_of_remote))); changing that to using 'SID' as shown previously made it all work.
There are currently no comments...