Oracle Database Links
Version 10.2
 
General Information
Related Data Dictionary Objects link$
dba_db_links all_db_links user_db_links
-
dbms_db_link ku$_dblink_view ora_kglr7_db_links
dbms_db_link_lib v_$dblink ku$_10_1_dblink_view
gv_$dblink wmp_api_dblink repcat$_repprop_dblink_how
ku$_dblink_t wmp_db_links_v v_$session_connect_info
System Privileges create database link
create public database link
drop public database link
Init.ora parameters related to Database Links dblink_encrypt_login

global_names (required to be TRUE for replication and for link names that are not identical to the service name)

open_links

open_links_per_instance
Notes:
  • The single quotes around the service name are mandatory
  • The service name must be TNSNAMES.ORA file on the server
 
Create Database Link
DB Link - Connected User CREATE [PUBLIC] DATABASE LINK <link_name>
USING '<service_name>';
CREATE PUBLIC DATABASE LINK conn_user
USING 'conn_link';

desc user_db_links

set linesize 121
col db_link format a20
col username format a20
col password format a20
col host format a20

SELECT * FROM user_db_links;

SELECT * FROM all_db_links;

SELECT table_name, tablespace_name FROM user_tables@conn_user;
DB Link - Current User CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO CURRENT_USER
USING '<service_name>';
CREATE DATABASE LINK curr_user
CONNECT TO CURRENT_USER
USING 'curr_link';

desc user_db_links

set linsize 121
col db_link format a20
col username format a20
col password format a20
col host format a20

SELECT * FROM user_db_links;

SELECT * FROM all_db_links;

SELECT table_name, tablespace_name FROM user_tables@curr_user;

-- The user who issues this statement must be a global user registered with the LDAP directory service.
DB Link - Fixed User CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING '<service_name>';
CREATE DATABASE LINK fix_user
CONNECT TO ids
IDENTIFIED BY ids
USING 'fix_link';

SELECT * FROM user_db_links;

SELECT * FROM all_db_links;

 SELECT * FROM v_$session_connect_info;

SELECT table_name, tablespace_name FROM user_tables@fix_user;
DB Link - Shared CREATE SHARED DATABASE LINK <link_name>
AUTHENTICATED BY <schema_name> IDENTIFIED BY <password>
USING '<service_name>';
CREATE SHARED DATABASE LINK testlink
AUTHENTICATED BY ids IDENTIFIED BY ids
USING 'orabase';

SELECT * FROM user_db_links;

SELECT * FROM all_db_links;

SELECT table_name, tablespace_name FROM user_tables@testlink;
 
Close Database Link
Close Link ALTER SESSION CLOSE DATABASE LINK <link_name>;
ALTER SESSION CLOSE DATABASE LINK test_link;
 
Drop Database Link
Drop Standard Link DROP DATABASE LINK <link_name>;
DROP DATABASE LINK test_link;
Drop Public Link DROP PUBLIC DATABASE LINK <link_name>;
DROP PUBLIC DATABASE LINK test_link;
 
Database Link Security
Fixed User Caution
In earlier versions 		SELECT db_link, username, password, host, created
FROM user_db_links;

conn / as sysdba

desc link$

col host format a20
col name format a20
col authpwdx format a40

SELECT name, userid, authpwdx
FROM link$;
 
Querying Across Database Links
Hint By default Oracle selects the site, local or remote, on which to perform the operation. A specific site can be selected by the developer using the DRIVING_SITE hint.
Test Link BEGIN
  ALTER SESSION CLOSE DATABASE LINK remove_db;

  SELECT table_name
  INTO i
  FROM all_tables@remote_db
  WHERE rownum = 1;
EXCEPTION
  WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20999, 'No Cnxn');
END;
 
Remote PL/SQL
Executing Remote Procedures <procedure_name>@<database_link>(<parameters>);
exec testproc@remotedb(1);

or

CREATE OR REPLACE SYNONYM testproc FOR remotedb.testproc;

exec testproc(1);

Managing remote dependencies for functions, packages, procedures, and types
  • If the initialization parameter file contains the following specification:

    REMOTE_DEPENDENCIES_MODE = TIMESTAMP

    Then only timestamps are used to resolve dependencies (if this is not explicitly overridden dynamically).
  • If the initialization parameter file contains the following parameter specification:

    REMOTE_DEPENDENCIES_MODE = SIGNATURE

    Then signatures are used to resolve dependencies (if this not explicitly overridden dynamically).
  • You can alter the mode dynamically by using the DDL statements. For example, this example alters the dependency model for the current session:

    ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = 
    {SIGNATURE | TIMESTAMP}

    Thise example alters the dependency model systemwide after startup:
    ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = 
    {SIGNATURE | TIMESTAMP}

If the REMOTE_DEPENDENCIES_MODE parameter is not specified, either in the init.ora parameter file or using the ALTER SESSION or ALTER SYSTEM DDL statements, then timestamp is the default value. Therefore, unless you explicitly use the REMOTE_DEPENDENCIES_MODE parameter, or the appropriate DDL statement, your server is operating using the timestamp dependency model.

When you use REMOTE_DEPENDENCIES_MODE=SIGNATURE:

  • If you change the default value of a parameter of a remote procedure, then the local procedure calling the remote procedure is not invalidated. If the call to the remote procedure does not supply the parameter, then the default value is used. In this case, because invalidation/recompilation does not automatically occur, the old default value is used. If you want to see the new default values, then you must recompile the calling procedure manually.
  • If you add a new overloaded procedure in a package (a new procedure with the same name as an existing one), then local procedures that call the remote procedure are not invalidated. If it turns out that this overloading results in a rebinding of existing calls from the local procedure under the timestamp mode, then this rebinding does not happen under the signature mode, because the local procedure does not get invalidated. You must recompile the local procedure manually to achieve the new rebinding.
  • If the types of parameters of an existing packaged procedure are changed so that the new types have the same shape as the old ones, then the local calling procedure is not invalidated or recompiled automatically. You must recompile the calling procedure manually to get the semantics of the new type.

Dependency Resolution

When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value), dependencies among program units are handled by comparing timestamps at runtime. If the timestamp of a called remote procedure does not match the timestamp of the called procedure, then the calling (dependent) unit is invalidated and must be recompiled. In this case, if there is no local PL/SQL compiler, then the calling application cannot proceed.

In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL compiler, then recompilation happens automatically when the calling procedure is run.

When REMOTE_DEPENDENCIES_MODE = SIGNATURE, the recorded timestamp in the calling unit is first compared to the current timestamp in the called remote unit. If they match, then the call proceeds. If the timestamps do not match, then the signature of the called remote subprogram, as recorded in the calling subprogram, is compared with the current signature of the called subprogram. If they do not match (using the criteria described in the section "When Does a Signature Change?"), then an error is returned to the calling session.
Suggestions for Managing Dependencies

Follow these guidelines for setting the REMOTE_DEPENDENCIES_MODE parameter:

  • Server-side PL/SQL users can set the parameter to TIMESTAMP (or let it default to that) to get the timestamp dependency mode.
  • Server-side PL/SQL users can choose to use the signature dependency mode if they have a distributed system and they want to avoid possible unnecessary recompilations.
  • Client-side PL/SQL users should set the parameter to SIGNATURE. This allows:
    1. Installation of new applications at client sites, without the need to recompile procedures.
    2.  Ability to upgrade the server, without encountering timestamp mismatches.
  • When using signature mode on the server side, add new procedures to the end of the procedure (or function) declarations in a package specification. Adding a new procedure in the middle of the list of declarations can cause unnecessary invalidation and recompilation of dependent procedures.
 
Related Topics
DBMS_SESSION
Hints