30 Managing a Distributed Database

Understanding How Global Database Names Are Formed

A global database name is formed from two components: a database name and a domain. The database name and the domain name are determined by the following initialization parameters at database creation:

These are examples of valid global database names:

The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.

Determining Whether Global Naming Is Enforced

The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database global database name as the name of the link. For example, if you are connected to the local hq server and want to create a link to the remote mfg database, and mfg enforces global naming, then you must use the mfg global database name as the link name.

You can also use service names as part of the database link name. For example, if you use the service names sn1 and sn2 to connect to database hq.acme.com, and hq enforces global naming, then you can create the following link names to hq:

• HQ.ACME.COM@SN1

• HQ.ACME.COM@SN2

  See Also:

  "Using Connection Qualifiers to Specify Service Names Within Link Names" for more information about using services names in link names

To determine whether global naming on a database is enforced on a database, either examine the database initialization parameter file or query the V$PARAMETER view. For example, to see whether global naming is enforced on mfg, you could start a session on mfg and then create and execute the following globalnames.sql script (sample output included):

COL NAME FORMAT A12
COL VALUE FORMAT A6
SELECT NAME, VALUE FROM V$PARAMETER
   WHERE NAME = 'global_names'
/

SQL> @globalnames

NAME         VALUE
------------ ------
global_names FALSE

Viewing a Global Database Name

Use the data dictionary view GLOBAL_NAME to view the database global name. For example, issue the following:

SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
-------------------------------------------------------------------------------
SALES.AU.ORACLE.COM

Changing the Domain in a Global Database Name

Use the ALTER DATABASE statement to change the domain in a database global name. Note that after the database is created, changing the initialization parameter DB_DOMAIN has no effect on the global database name or on the resolution of database link names.

The following example shows the syntax for the renaming statement, where database is a database name and domain is the network domain:

ALTER DATABASE RENAME GLOBAL_NAME TO database.domain;

Use the following procedure to change the domain in a global database name:

1. Determine the current global database name. For example, issue:

   SELECT * FROM GLOBAL_NAME;
   
   GLOBAL_NAME
   ----------------------------------------------------------------------------
   SALES.AU.ORACLE.COM
   
   

2. Rename the global database name using an ALTER DATABASE statement. For example, enter:

   ALTER DATABASE RENAME GLOBAL_NAME TO sales.us.oracle.com;
   
   

3. Query the GLOBAL_NAME table to check the new name. For example, enter:

   SELECT * FROM GLOBAL_NAME;
   
   GLOBAL_NAME
   ----------------------------------------------------------------------------
   SALES.US.ORACLE.COM
   

Changing a Global Database Name: Scenario

In this scenario, you change the domain part of the global database name of the local database. You also create database links using partially specified global names to test how Oracle Database resolves the names. You discover that the database resolves the partial names using the domain part of the current global database name of the local database, not the value for the initialization parameter DB_DOMAIN.

1. You connect to SALES.US.ACME.COM and query the GLOBAL_NAME data dictionary view to determine the current database global name:

   CONNECT SYSTEM/password@sales.us.acme.com
   SELECT * FROM GLOBAL_NAME;
   
   GLOBAL_NAME
   ---------------------------------------------------------------------------- 
   SALES.US.ACME.COM
   
   

2. You query the V$PARAMETER view to determine the current setting for the DB_DOMAIN initialization parameter:

   SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain'; 
   
   NAME       VALUE
   ---------  -----------
   db_domain  US.ACME.COM 
   
   

3. You then create a database link to a database called hq, using only a partially-specified global name:

   CREATE DATABASE LINK hq USING 'sales'; 
   
   

   The database expands the global database name for this link by appending the domain part of the global database name of the local database to the name of the database specified in the link.

4. You query USER_DB_LINKS to determine which domain name the database uses to resolve the partially specified global database name:

   SELECT DB_LINK FROM USER_DB_LINKS; 
   
   DB_LINK
   ------------------
   HQ.US.ACME.COM
   
   

   This result indicates that the domain part of the global database name of the local database is us.acme.com. The database uses this domain in resolving partial database link names when the database link is created.

5. Because you have received word that the sales database will move to Japan, you rename the sales database to sales.jp.acme.com:

   ALTER DATABASE RENAME GLOBAL_NAME TO sales.jp.acme.com;
   SELECT * FROM GLOBAL_NAME; 
   
   GLOBAL_NAME
   ---------------------------------------------------------------------------- 
   SALES.JP.ACME.COM
   
   

6. You query V$PARAMETER again and discover that the value of DB_DOMAIN is not changed, although you renamed the domain part of the global database name:

   SELECT NAME, VALUE FROM V$PARAMETER 
     WHERE NAME = 'db_domain'; 
   
   NAME       VALUE
   ---------  -----------
   db_domain  US.ACME.COM 
   
   

   This result indicates that the value of the DB_DOMAIN initialization parameter is independent of the ALTER DATABASE RENAME GLOBAL_NAME statement. The ALTER DATABASE statement determines the domain of the global database name, not the DB_DOMAIN initialization parameter (although it is good practice to alter DB_DOMAIN to reflect the new domain name).

7. You create another database link to database supply, and then query USER_DB_LINKS to see how the database resolves the domain part of the global database name of supply:

   CREATE DATABASE LINK supply USING 'supply'; 
   SELECT DB_LINK FROM USER_DB_LINKS; 
   
   DB_LINK
   ------------------
   HQ.US.ACME.COM 
   SUPPLY.JP.ACME.COM
   
   

   This result indicates that the database resolves the partially specified link name by using the domain jp.acme.com. This domain is used when the link is created because it is the domain part of the global database name of the local database. The database does not use the DB_DOMAIN initialization parameter setting when resolving the partial link name.

8. You then receive word that your previous information was faulty: sales will be in the ASIA.JP.ACME.COM domain, not the JP.ACME.COM domain. Consequently, you rename the global database name as follows:

   ALTER DATABASE RENAME GLOBAL_NAME TO sales.asia.jp.acme.com; 
   SELECT * FROM GLOBAL_NAME; 
   
   GLOBAL_NAME
   ---------------------------------------------------------------------------- 
   SALES.ASIA.JP.ACME.COM
   
   

9. You query V$PARAMETER to again check the setting for the parameter DB_DOMAIN:

   SELECT NAME, VALUE FROM V$PARAMETER 
     WHERE NAME = 'db_domain'; 
   
   NAME        VALUE
   ----------  -----------
   db_domain   US.ACME.COM 
   
   

   The result indicates that the domain setting in the parameter file is exactly the same as it was before you issued either of the ALTER DATABASE RENAME statements.

10. Finally, you create a link to the warehouse database and again query USER_DB_LINKS to determine how the database resolves the partially-specified global name:

    CREATE DATABASE LINK warehouse USING 'warehouse'; 
    SELECT DB_LINK FROM USER_DB_LINKS; 
    
    DB_LINK
    ------------------
    HQ.US.ACME.COM 
    SUPPLY.JP.ACME.COM
    WAREHOUSE.ASIA.JP.ACME.COM
    
    

    Again, you see that the database uses the domain part of the global database name of the local database to expand the partial link name during link creation.

    Note:

    In order to correct the supply database link, it must be dropped and re-created.

    See Also:

    Oracle Database Reference for more information about specifying the DB_NAME and DB_DOMAIN initialization parameters

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:

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

CREATE DATABASE LINK link_name ...;

CREATE PUBLIC DATABASE LINK link_name ...;

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.

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

CREATE [SHARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];

CREATE DATABASE LINK sales.division3.acme.com USING 'sales';

CREATE [SHARED] [PUBLIC] DATABASE LINK dblink CONNECT TO CURRENT_USER 
[USING 'net_service_name'];

CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';

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.acme.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.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 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.acme.com@hq_1

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

Determining Whether to Use Shared Database Links

Look carefully at your application and shared server configuration to determine whether to use shared links. A simple guideline is to use shared database links when the number of users accessing a database link is expected to be much larger than the number of server processes in the local database.

The following table illustrates three possible configurations involving database links:

Shared database links are not useful in all situations. Assume that only one user accesses the remote server. If this user defines a shared database link and 10 shared server processes exist in the local database, then this user can require up to 10 network connections to the remote server. Because the user can use each shared server process, each process can establish a connection to the remote server.

Clearly, a nonshared database link is preferable in this situation because it requires only one network connection. Shared database links lead to more network connections in single-user scenarios, so use shared links only when many users need to use the same link. Typically, shared links are used for public database links, but can also be used for private database links when many clients access the same local schema (and therefore the same private database link).

Creating Shared Database Links

To create a shared database link, use the keyword SHARED in the CREATE DATABASE LINK statement:

CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_name'];

Whenever you use the keyword SHARED, the clause AUTHENTICATED BY is required. The schema specified in the AUTHENTICATED BY clause must exist in the remote database and must be granted at least the CREATE SESSION privilege. The credentials of this schema can be considered the authentication method between the local database and the remote database. These credentials are required to protect the remote shared server processes from clients that masquerade as a database link user and attempt to gain unauthorized access to information.

After a connection is made with a shared database link, operations on the remote database proceed with the privileges of the CONNECT TO user or CURRENT_USER, not the AUTHENTICATED BY schema.

The following example creates a fixed user, shared link to database sales, connecting as scott and authenticated as linkuser:

CREATE SHARED DATABASE LINK link2sales
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY linkuser IDENTIFIED BY ostrich
USING 'sales';

Configuring Shared Database Links

You can configure shared database links in the following ways:

• Creating Shared Links to Dedicated Servers

• Creating Shared Links to Shared Servers

Creating Shared Links to Dedicated Servers

In the configuration illustrated in Figure 30-1, a shared server process in the local server owns a dedicated remote server process. The advantage is that a direct network transport exists between the local shared server and the remote dedicated server. A disadvantage is that extra back-end server processes are needed.

Note:

The remote server can either be a shared server or dedicated server. There is a dedicated connection between the local and remote servers. When the remote server is a shared server, you can force a dedicated server connection by using the (SERVER=DEDICATED) clause in the definition of the service name.

Figure 30-1 A Shared Database Link to Dedicated Server Processes

Description of "Figure 30-1 A Shared Database Link to Dedicated Server Processes"

Creating Shared Links to Shared Servers

The configuration illustrated in Figure 30-2 uses shared server processes on the remote server. This configuration eliminates the need for more dedicated servers, but requires the connection to go through the dispatcher on the remote server. Note that both the local and the remote server must be configured as shared servers.

Figure 30-2 Shared Database Link to Shared Server

Description of "Figure 30-2 Shared Database Link to Shared Server"

See Also:

Oracle Database Net Services Administrator's Guide for information about the shared server option

Closing Database Links

If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link. This situation has the following consequences:

• If 20 users open sessions and access the same public link in a local database, then 20 database link connections are open.

• If 20 users open sessions and each user accesses a private link, then 20 database link connections are open.

• If one user starts a session and accesses 20 different links, then 20 database link connections are open.

After you close a session, the links that were active in the session are automatically closed. You may have occasion to close the link manually. For example, close links when:

• The network connection established by a link is used infrequently in an application.

• The user session must be terminated.

If you want to close a link, issue the following statement, where linkname refers to the name of the link:

ALTER SESSION CLOSE DATABASE LINK linkname;

Note that this statement only closes the links that are active in your current session.

Dropping Database Links

You can drop a database link just as you can drop a table or view. If the link is private, then it must be in your schema. If the link is public, then you must have the DROP PUBLIC DATABASE LINK system privilege.

The statement syntax is as follows, where dblink is the name of the link:

DROP [PUBLIC] DATABASE LINK dblink; 

Limiting the Number of Active Database Link Connections

You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.

Note the following considerations for setting this parameter:

• The value should be greater than or equal to the number of databases referred to in a single SQL statement that references multiple databases.

• Increase the value if several distributed databases are accessed over time. Thus, if you regularly access three databases, set OPEN_LINKS to 3 or greater.

• The default value for OPEN_LINKS is 4. If OPEN_LINKS is set to 0, then no distributed transactions are allowed.

  See Also:

  Oracle Database Reference for more information about the OPEN_LINKS initialization parameter

Determining Which Links Are in the Database

The following views show the database links that have been defined at the local database and stored in the data dictionary:

These data dictionary views contain the same basic information about database links, with some exceptions:

Any user can query USER_DB_LINKS to determine which database links are available to that user. Only those with additional privileges can use the ALL_DB_LINKS or DBA_DB_LINKS view.

The following script queries the DBA_DB_LINKS view to access link information:

COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/

Here, the script is invoked and the resulting output is shown:

SQL>@link_script

OWNER      DB_LINK                        USER     SERVICE CREATED
---------- ------------------------------ -------- ------- ----------
SYS        TARGET.US.ACME.COM             SYS      inst1   23-JUN-99
PUBLIC     DBL1.UK.ACME.COM               BLAKE    ora51   23-JUN-99
PUBLIC     RMAN2.US.ACME.COM                       inst2   23-JUN-99
PUBLIC     DEPT.US.ACME.COM                        inst2   23-JUN-99
JANE       DBL.UK.ACME.COM                BLAKE    ora51   23-JUN-99
SCOTT      EMP.US.ACME.COM                SCOTT    inst2   23-JUN-99
6 rows selected.

Determining Which Link Connections Are Open

You may find it useful to determine which database link connections are currently open in your session. Note that if you connect as SYSDBA, you cannot query a view to determine all the links open for all sessions; you can only access the link information in the session within which you are working.

The following views show the database link connections that are currently open in your current session:

These data dictionary views contain the same basic information about database links, with one exception:

For example, you can create and execute the script below to determine which links are open (sample output included):

COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK
/

SQL> @dblink

DB_LINK                   OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE  C_P_S
------------------------- ------ ----- ----- -------- ------- --- ------ ------
INST2.ACME.COM                 0 YES   YES    UNKN          0 YES YES       255

Using Views to Create Location Transparency

Local views can provide location transparency for local and remote tables in a distributed database system.

For example, assume that table emp is stored in a local database and table dept is stored in a remote database. To make these tables transparent to users of the system, you can create a view in the local database that joins local and remote data:

CREATE VIEW company AS
   SELECT a.empno, a.ename, b.dname 
   FROM scott.emp a, jward.dept@hq.acme.com b 
   WHERE a.deptno = b.deptno; 

Figure 30-3 Views and Location Transparency

Description of "Figure 30-3 Views and Location Transparency"

When users access this view, they do not need to know where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required information. For example, the following query provides data from both the local and remote database table:

SELECT * FROM company; 

The owner of the local view can grant only those object privileges on the local view that have been granted by the remote user. (The remote user is implied by the type of database link). This is similar to privilege management for views that reference local data.

Using Synonyms to Create Location Transparency

Synonyms are useful in both distributed and non-distributed environments because they hide the identity of the underlying object, including its location in a distributed database system. If you must rename or move the underlying object, you only need to redefine the synonym; applications based on the synonym continue to function normally. Synonyms also simplify SQL statements for users in a distributed database system.

CREATE [PUBLIC] synonym_name 
FOR [schema.]object_name[@database_link_name]; 

CREATE PUBLIC SYNONYM emp FOR scott.emp@hq.acme.com; 

Using Procedures to Create Location Transparency

PL/SQL program units called procedures can provide location transparency. You have these options:

• Using Local Procedures to Reference Remote Data

• Using Local Procedures to Call Remote Procedures

• Using Local Synonyms to Reference Remote Procedures

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

CREATE SYNONYM emp FOR emp@hq.acme.com; 

CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
  DELETE FROM emp WHERE empno = enum; 
END; 

CONNECT scott/tiger@local_db

CREATE PROCEDURE fire_emp (enum NUMBER) 
AS 
BEGIN 
  EXECUTE term_emp@hq.acme.com;
END; 

CONNECT scott/tiger@hq.acme.com

CREATE PROCEDURE term_emp (enum NUMBER) 
AS 
BEGIN 
  DELETE FROM emp WHERE empno = enum;
END;

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

SQL> CONNECT peggy/hill@supply
SQL> CREATE PUBLIC SYNONYM emp FOR scott.fire_emp@sales.acme.com; 

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/doe@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/sculley@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/oracle@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/mulder@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/young@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/simpson@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/tiger@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/fairlane@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.