Creating Database Links

To support application access to the data and schema objects throughout a distributed database system, you must create all necessary database links. This section contains the following topics:

Obtaining Privileges Necessary for Creating Database Links
Specifying Link Types
Specifying Link Users
Using Connection Qualifiers to Specify Service Names Within Link Names

Obtaining Privileges Necessary for Creating Database Links

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:

Privilege	Database	Required For
`CREATE DATABASE LINK`	Local	Creation of a private database link.
`CREATE PUBLIC DATABASE LINK`	Local	Creation of a public database link.
`CREATE SESSION`	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

Specifying Link Types

When you create a database link, you must decide who will have access to it. The following sections describe how to create the three basic types of links:

Creating Private Database Links
Creating Public Database Links
Creating Global Database Links

Creating Private Database Links

To create a private database link, specify the following (where link_name is the global database name or an arbitrary link name):

CREATE DATABASE LINK link_name ...;

Following are examples of private database links:

SQL Statement	Result
`CREATE DATABASE LINK supply.us.example.com;`	A private link using the global database name to the remote `supply` database. The link uses the userid/password of the connected user. So if `scott` (identified by `password`) uses the link in a query, the link establishes a connection to the remote database as `scott/password`.
`CREATE DATABASE LINK link_2 CONNECT TO jane IDENTIFIED BY` `password` `USING 'us_supply';`	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/password` regardless of the connected user.
`CREATE DATABASE LINK link_1 CONNECT TO CURRENT_USER USING 'us_supply';`	A private link called `link_1` to the database with service name `us_supply`. The link uses the userid/password of the current user to log onto the remote database. 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.

Creating Public Database Links

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:

SQL Statement	Result
`CREATE PUBLIC DATABASE LINK supply.us.example.com;`	A public link to the remote `supply` database. The link uses the userid/password of the connected user. So if `scott` (identified by `password`) uses the link in a query, the link establishes a connection to the remote database as `scott/password`.
`CREATE PUBLIC DATABASE LINK pu_link CONNECT TO CURRENT_USER USING 'supply';`	A public link called `pu_link` to the database with service name `supply`. The link uses the userid/password of the current user to log onto the remote database. 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").
`CREATE PUBLIC DATABASE LINK sales.us.example.com CONNECT TO jane IDENTIFIED BY` `password;`	A public fixed user link to the remote `sales` database. The link connects to the remote database with the userid/password of `jane/password`.

Creating Global Database Links

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.

Specifying Link Users

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.

Creating Fixed User Database Links

To create a fixed user database link, you embed the credentials (in this case, a username and password) required to access the remote database in the definition of the link:

CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;

Following are examples of fixed user database links:

SQL Statement	Result
`CREATE PUBLIC DATABASE LINK supply.us.example.com CONNECT TO scott AS` `password;`	A public link using the global database name to the remote `supply` database. The link connects to the remote database with the userid/password `scott/password`.
`CREATE DATABASE LINK foo CONNECT TO jane IDENTIFIED BY` `password` `USING 'finance';`	A private fixed user link called `foo` to the database with service name `finance`. The link connects to the remote database with the userid/password `jane/password`.

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.

Creating Connected User and Current User Database Links

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".

Creating a Connected User Database Link

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.example.com USING 'sales';

Creating a Current User Database Link

To create a current user database link, use the CONNECT TO CURRENT_USER clause in the link creation statement. Current user links are only available through the Oracle Advanced Security option.

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.

Using Connection Qualifiers to Specify Service Names Within Link Names

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.example.com is managed in a Oracle Real Application Clusters environment. The hq database has two instances named hq_1 and hq_2. The local database can contain the following public database links to define pathways to the remote instances of the hq database:

CREATE PUBLIC DATABASE LINK hq.example.com@hq_1
  USING 'string_to_hq_1';
CREATE PUBLIC DATABASE LINK hq.example.com@hq_2
  USING 'string_to_hq_2';
CREATE PUBLIC DATABASE LINK hq.example.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 USING string.

To use a service name to specify a particular instance, include the service name at the end of the global object name:

SELECT * FROM scott.emp@hq.example.com@hq_1

Note that in this example, there are two @ symbols.