Managing a Distributed Database: Examples

Example 1: Creating a Public Fixed User Database Link

The following statements connect to the local database as jane and create a public fixed user database link to database sales for scott. The database is accessed through its net service name sldb:

CONNECT jane@local

CREATE PUBLIC DATABASE LINK sales.division3.acme.com
  CONNECT TO scott IDENTIFIED BY tiger
  USING 'sldb';

After executing these statements, any user connected to the local database can use the sales.division3.acme.com database link to connect to the remote database. Each user connects to the schema scott in the remote database.

To access the table emp table in scott's remote schema, a user can issue the following SQL query:

SELECT * FROM emp@sales.division3.acme.com;

Note that each application or user session creates a separate connection to the common account on the server. The connection to the remote database remains open for the duration of the application or user session.

Example 2: Creating a Public Fixed User Shared Database Link

The following example connects to the local database as dana and creates a public link to the sales database (using its net service name sldb). The link allows a connection to the remote database as scott and authenticates this user as scott:

CONNECT dana@local

CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com
  CONNECT TO scott IDENTIFIED BY tiger 
  AUTHENTICATED BY scott IDENTIFIED BY tiger
  USING 'sldb';

Now, any user connected to the local shared server can use this database link to connect to the remote sales database through a shared server process. The user can then query tables in the scott schema.

In the preceding example, each local shared server can establish one connection to the remote server. Whenever a local shared server process needs to access the remote server through the sales.division3.acme.com database link, the local shared server process reuses established network connections.

Example 3: Creating a Public Connected User Database Link

The following example connects to the local database as larry and creates a public link to the database with the net service name sldb:

CONNECT larry@local

CREATE PUBLIC DATABASE LINK redwood
  USING 'sldb';

Any user connected to the local database can use the redwood database link. The connected user in the local database who uses the database link determines the remote schema.

If scott is the connected user and uses the database link, then the database link connects to the remote schema scott. If fox is the connected user and uses the database link, then the database link connects to remote schema fox.

The following statement fails for local user fox in the local database when the remote schema fox cannot resolve the emp schema object. That is, if the fox schema in the sales.division3.acme.com does not have emp as a table, view, or (public) synonym, an error will be returned.

CONNECT fox@local

SELECT * FROM emp@redwood;

Example 4: Creating a Public Connected User Shared Database Link

The following example connects to the local database as neil and creates a shared, public link to the sales database (using its net service name sldb). The user is authenticated by the userid/password of crazy/horse. The following statement creates a public, connected user, shared database link:

CONNECT neil@local

CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com 
  AUTHENTICATED BY crazy IDENTIFIED BY horse
  USING 'sldb';

Each user connected to the local server can use this shared database link to connect to the remote database and query the tables in the corresponding remote schema.

Each local, shared server process establishes one connection to the remote server. Whenever a local server process needs to access the remote server through the sales.division3.acme.com database link, the local process reuses established network connections, even if the connected user is a different user.

If this database link is used frequently, eventually every shared server in the local database will have a remote connection. At this point, no more physical connections are needed to the remote server, even if new users use this shared database link.

Example 5: Creating a Public Current User Database Link

The following example connects to the local database as the connected user and creates a public link to the sales database (using its net service name sldb). The following statement creates a public current user database link:

CONNECT bart@local

CREATE PUBLIC DATABASE LINK sales.division3.acme.com
  CONNECT TO CURRENT_USER
  USING 'sldb';

The consequences of this database link are as follows:

Assume scott creates local procedure fire_emp that deletes a row from the remote emp table, and grants execute privilege on fire_emp to ford.

CONNECT scott@local_db

CREATE PROCEDURE fire_emp (enum NUMBER) 
AS
BEGIN
   DELETE FROM emp@sales.division3.acme.com
   WHERE empno=enum;
END;

GRANT EXECUTE ON fire_emp TO ford;

Now, assume that ford connects to the local database and runs scott's procedure:

CONNECT ford@local_db

EXECUTE PROCEDURE scott.fire_emp (enum 10345);

When ford executes the procedure scott.fire_emp, the procedure runs under scott's privileges. Because a current user database link is used, the connection is established to scott's remote schema, not ford's remote schema. Note that scott must be a global user while ford does not have to be a global user.

You can accomplish the same result by using a fixed user database link to scott's remote schema.