Database links are used in distributed Oracle environments to define a communications path between two databases. A database link defines a network connection, and, optionally, a username, to use in contacting a remote database. The purpose of using a link is to isolate SQL statements from the underlying, physical network topology. If the location of a remote database changes, only the link need be updated.
A database link is usually defined in a specific database, and may be used only by users of that database. If you want to use the same database link name from all databases in your network, you must identically define links with that name in each of your databases. As you might imagine, as the number of links increases, this can become a maintenance nightmare. Enter the global database link. A global database link is one that you define in a central location, and is used from all databases.
Recently, I decided to do a little research into global database links and how they are created. First, I found that global database links can only be defined in Oracle Names (DNS-like name resolution software), so if you're not an Oracle Names user, you can't take advantage of them. I do use Oracle Names, so I was OK on that point. The second thing I discovered was that Oracle's online documentation (You've got to be an Oracle Technology Network member to use it.) is frustratingly vague on how to actually define a global database link, and also on how to configure your environment to enable them to be used. Here's a quick summary of what I discovered after some research:
|
Related Reading 
Oracle Net8 Configuration and Troubleshooting
Table of Contents
Index Sample Chapter Author's Article Read Online--Safari Search this book on Safari: |
db_name = herman db_domain = gennick.org
Once a database has been created, the global name is stored within the control file, and the parameter values in the initialization file no longer matter. To change a global name for an existing database, you must use the ALTER DATABASE RENAME GLOBAL_NAME command.
Oracle feels strongly enough about this naming convention that they even provide an enforcement mechanism for you to use. By adding the following parameter setting to all your parameter files, you effectively prevent anyone from using a database link with a name that violates this naming convention:
GLOBAL_NAMES = TRUE
It turns out that this has ramifications for global database links. Oracle Names doesn't even appear to support the creation of a global database link with a name that doesn't match the target database's global name. So if you are using Oracle Names, you must follow the preferred naming convention.
USE_PLUG_AND_PLAY_LISTENER = ON
The important thing to know about this setting with respect to global database links is that it causes the Net8 listener to register global database names with Oracle Names servers. Any databases that you register with the listener are now also automatically registered with Oracle Names.
The really odd thing about defining a global database link, at least using Net8 Assistant, is that I was allowed to only define the link name. Usually, you define a database link in terms of both a link name and a Net service name that points to the destination database. The fact that I could only define the link name meant that Oracle Names must be making some assumptions about the target database. Oracle Names must assume that the link name matches a global database name that has been registered by the Net8 listener. In my case, this was as a result of my enabling the USE_PLUG_AND_PLAY option. This underscores the importance of following Oracle's preferred naming convention, and also of enabling plug and play for your listener.
SQL> SELECT * FROM dual@donna.gennick.org; D - X
Not only did my new link work, but it worked from all my databases. By using
Oracle Names to create a global database link, I was able to define the link
just once. No longer do I need to define the same link multiple times in each
database. I can now define global database links centrally in Oracle Names, and they will immediately be available across my network.
Jonathan Gennick is a writer and an editor at O'Reilly & Associates. His writing career began in 1997, when he coauthored Teach Yourself PL/SQL in 21 Days (Sams). Since then, he has written Oracle SQL*Plus: The Definitive Guide (1998) and Oracle SQL*Plus Pocket Reference (2000), both for O'Reilly & Associates. In his previous life, Jonathan was a manager and a database administrator in KPMG Consulting's Public Services practice. He has more than a decade of experience with relational databases.
O'Reilly & Associates will soon release (December 2000) Oracle Net8 Configuration and Troubleshooting.
Copyright © 2007 O'Reilly Media, Inc.