Oracle DBA – A lifelong learning experience

Creating a database link using a tnsnames entry

Posted by John Hallas on December 29, 2017

I have had this in my draft posts for a couple of weeks and I refrained from publishing because it was too simple.

However the main purpose of this post is to help others save time by providing useful information. The secondary purpose of to remind myself how I overcame whatever problem is being covered.

A user did not have permissions to edit the tnsnames.ora file on a server so I created the database link for him to use the connect description, thereby circumventing the need for an update to the tnsnames.ora file.

I prefer database links to be named to show the database they connect to, however in this case they wanted a standard name which is used in code and can be used across all the environments.

 create database link ebsdev connect to remote_username  identified by password using
‘EBSDEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = 1531))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EBSDEV)
)
)’;
select ‘x’ from dual@ebsdev;
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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

 
%d bloggers like this: