A database link is a pointer in the local database that lets you access objects on a remote database. To create a private database link, you must have been granted the proper privileges. The following table illustrates which privileges are required on which database for which type of link:
||Local||Creation of a private database link.|
||Local||Creation of a public database link.|
||Remote||Creation of any type of database link.|
To see which privileges you currently have available, query
ROLE_SYS_PRIVS. For example, you could create and execute the following
privs.sql script (sample output included):
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK', 'CREATE PUBLIC DATABASE LINK') / SQL> @privs Database Link Privileges ---------------------------------------- CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK CREATE SESSION
CREATE DATABASE LINK link_name ...;
Following are examples of private database links:
||A private link using the global database name to the remote
The link uses the userid/password of the connected user. So if
||A private fixed user link called link_2 to the database with service name us_supply. The link connects to the remote database with the userid/password of jane/doe regardless of the connected user.|
||A private link called
Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links"). Current user links are part of the Oracle Advanced Security option.
See Also:Oracle Database SQL Language Reference for
CREATE DATABASE LINKsyntax
To create a public database link, use the keyword
PUBLIC (where link_name is the global database name or an arbitrary link name):
CREATE PUBLIC DATABASE LINK link_name ...;
Following are examples of public database links:
||A public link to the remote
||A public link called
Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links").
||A public fixed user link to the remote
See Also:Oracle Database SQL Language Reference for
CREATE PUBLIC DATABASE LINKsyntax
In earlier releases, you defined global database links in the Oracle Names server. The Oracle Names server has been deprecated. Now, you can use a directory server in which databases are identified by net service names. In this document these are what are referred to as global database links.
See the Oracle Database Net Services Administrator's Guide to learn how to create directory entries that act as global database links.
A database link defines a communication path from one database to another. When an application uses a database link to access a remote database, Oracle Database establishes a database session in the remote database on behalf of the local application request.
When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links.
CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;
Following are examples of fixed user database links:
||A public link using the global database name to the remote
||A private fixed user link called
When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the fixed user's credentials across the network when an application uses the link to access the remote database.
Connected user and current user database links do not include credentials in the definition of the link. The credentials used to connect to the remote database can change depending on the user that references the database link and the operation performed by the application.
Note:For many distributed applications, you do not want a user to have privileges in a remote database. One simple way to achieve this result is to create a procedure that contains a fixed user or current user database link within it. In this way, the user accessing the procedure temporarily assumes someone else's privileges.
For an extended conceptual discussion of the distinction between connected users and current users, see "Users of Database Links".
To create a connected user database link, omit the
CONNECT TO clause. The following syntax creates a connected user database link, where dblink is the name of the link and net_service_name is an optional connect string:
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];
For example, to create a connected user database link, use the following syntax:
CREATE DATABASE LINK sales.division3.acme.com USING 'sales';
The following syntax creates a current user database link, where dblink is the name of the link and net_service_name is an optional connect string:
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink CONNECT TO CURRENT_USER [USING 'net_service_name'];
For example, to create a connected user database link to the
sales database, you might use the following syntax:
CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';
Note:To use a current user database link, the current user must be a global user on both databases involved in the link.
See Also:Oracle Database SQL Language Reference for more syntax information about creating database links
In some situations, you may want to have several database links of the same type (for example, public) that point to the same remote database, yet establish connections to the remote database using different communication pathways. Some cases in which this strategy is useful are:
A remote database is part of an Oracle Real Application Clusters configuration, so you define several public database links at your local node so that connections can be established to specific instances of the remote database.
Some clients connect to the Oracle Database server using TCP/IP while others use DECNET.
To facilitate such functionality, the database lets you create a database link with an optional service name in the database link name. When creating a database link, a service name is specified as the trailing portion of the database link name, separated by an
@ sign, as in
@sales. This string is called a connection qualifier.
For example, assume that remote database
hq.acme.com is managed in a Oracle Real Application Clusters environment. The
hq database has two instances named
hq_2. The local database can contain the following public database links to define pathways to the remote instances of the
CREATE PUBLIC DATABASE LINK hq.acme.com@hq_1 USING 'string_to_hq_1'; CREATE PUBLIC DATABASE LINK hq.acme.com@hq_2 USING 'string_to_hq_2'; CREATE PUBLIC DATABASE LINK hq.acme.com USING 'string_to_hq';
Notice in the first two examples that a service name is simply a part of the database link name. The text of the service name does not necessarily indicate how a connection is to be established; this information is specified in the service name of the
USING clause. Also notice that in the third example, a service name is not specified as part of the link name. In this case, just as when a service name is specified as part of the link name, the instance is determined by the
To use a service name to specify a particular instance, include the service name at the end of the global object name:
SELECT * FROM email@example.com@hq_1
Note that in this example, there are two @ symbols.