Home / Oracle DBA / Database Links

Database Links

Database Links

The central concept in distributed database systems is a database link. A database link is a connection between two physical database servers that allows a client to access them as one logical database.
What Are Database Links?
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry. A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.
Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access. One principal difference among database links is the way that connections to a remote database occur. Users access a remote database through the following types of links: Create database links using the CREATE DATABASE LINK statement. After a link is created, you can use it to specify schema objects in SQL statements.
What Are Shared Database Links?
A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously. When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode. The following table illustrates the possibilities: A shared database link can exist in any of these four configurations. Shared links differ from standard database links in the following ways:
Different users accessing the same schema object through a database link can share
a network connection.
Type of Link Description
Connected user link Users connect as themselves, which means that they must have an account on the remote database with the same user name and password as their account on the local database.
Fixed user link Users connect using the user name and password referenced in the link. For example, if Jane uses a fixed user link that connects to the hq database with the user name and password Scott/password, then she connects as Scott, Jane has all the privileges in hq granted to Scott directly, and all the default roles that Scott has been granted in the hq database.
Current user link A user connects as a global user. A local user can connect as a global user in the context of a stored procedure, without storing the global user’s password in a link definition. For example, Jane can access a procedure that Scott wrote, accessing Scott’s account and Scott’s schema on the hq database. Current user links are an aspect of Oracle Advanced Security.
When a user needs to establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server. The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions.
When you use a shared database link in a shared server configuration, a network connection is established directly out of the shared server process in the local server. For a non-shared database link on a local shared server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher.
Why Use Database Links?
The great advantage of database links is that they allow users to access another user’s objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database. For example, assume that employees submit expense reports to Accounts Payable(A/P), and further suppose that a user using an A/P application needs to retrieve information about employees from the hq database. The A/P users should be able to connect to the hq database and execute a stored procedure in the remote hq database that retrieves the desired information. The A/P users should not need to be hq database users to do their jobs; they should only be able to access hq information in a controlled way as limited by the procedure.
Global Database Names in Database Links
To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name. The database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter.
The name of a database is formed by starting at the leaf of the tree and following a path to the root. For example, the mfg database is in division3 of the acme_tools branch of the com domain. The global database name for mfg is created by concatenating the nodes in the tree as follows:
While several databases can share an individual name, each database must have a unique global database name. For example, the network domain sus.americas.acme_auto.com and uk.europe.acme_auto.com each contain a sales database. The global database naming system distinguishes the sales database in the Americas division from the sales database in the Europe division as follows:
Names for Database Links
Typically, a database link has the same name as the global database name of the remote database that it references. For example, if the global database name of a database is sales.us.example.com, then the database link is also called sales.us.example.com. When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for hq ishq.acme.com, and GLOBAL_NAMES is TRUE, then the link name must be called hq.acme.com. Note that the database checks the domain part of the global database name as stored in the data dictionary, not the DB_DOMAIN setting in the initialization parameter file (see “Changing the Domain in a Global Database Name” on page 32-3).If you set the initialization parameter GLOBAL_NAMES to FALSE, then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to hq.acme.com as foo. After you have enabled global naming, database links are essentially transparent tousers of a distributed database because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database to remote database sales:
CREATE PUBLIC DATABASE LINK sales.division3.acme.com USING ‘sales1’;
Types of Database Links
Oracle Database lets you create private, public, and global database links. These basic link types differ according to which users are allowed access to the remote database:
Note: Oracle recommends that you use global naming because many useful features, including Replication, require global naming.
Private User who created the link: Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.
Public User called PUBLIC: Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.
Global User called PUBLIC: Creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.
Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.
Database Link Restrictions
You cannot perform the following operations using database links:
Grant privileges on remote objects
Execute DESCRIBE operations on some remote objects. The following remote objects, however, do support DESCRIBE operations:
Analyze remote objects
Define or enforce referential integrity
Grant roles to users in a remote database
Obtain non default roles on a remote database. For example, if Jane connects to the local database and executes a stored procedure that uses a fixed user link connecting as Scott, Jane receives Scott’s default roles on the remote database. Jane cannot issue SET ROLE to obtain a non default role.
Execute hash query joins that use shared server connections
Use a current user link without authentication through SSL, password, or NT native authentication.
View More:
Oracle 10G Recylce Bin Features
Automatic Memory Management
Shared Server Architecture
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.