CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Database Links

Version 11.1
General Information
Related Data Dictionary Objects
link$    
all_db_links gv_$session_connect_info repcat$_repprop_dblink_how
dba_db_links ku$_dblink_t user_db_links
dbms_dblink ku$_dblink_view wmp_api_dblink
dbms_dblink_lib ku$_10_1_dblink_view wmp_db_links_v
gv_$dblink ora_kglr7_db_links  
Related Files $ORACLE_HOME/rdbms/admin/caths.sql
System Privileges create database link
create public database link
drop public database link

Init.ora parameters related to Database Links
global_names (required to be TRUE for replication. If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.

global_names
open_links
open_links_per_instance
conn / as sysdba

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%link%')
OR (name IN ('global_names', 'dblink_encrypt_login'));

GLOBAL_NAMES
The global_name is made up of the db_name and the db_domain, and the first element (before the first . in a global name is treated as the 'db_name' and the rest of the global_name is treated as the 'db_domain'.

~ Sybrand Bakker
set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name IN ('db_name', 'db_domain');

col value$ format a40
col comment$ format a40

SELECT *
FROM props$
WHERE name LIKE '%GLOBAL%';

ALTER DATABASE RENAME GLOBAL_NAME TO orabase.psoug.org;

SELECT *
FROM props$
WHERE name LIKE '%GLOBAL%';
Notes:
  • The single quotes around the service name are mandatory
  • The service name must be in the TNSNAMES.ORA file on the server
 
Create Database Link

Connected User Link
CREATE [SHARED] [PUBLIC] DATABASE LINK <link_name>
CONNECT TO CURRENT_USER
USING '<service_name>';
-- create tnsnames entry for conn_link
conn_link =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orabase)
    )
  )


conn uwclass/uwclass

CREATE 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;

Current User Link
CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO CURRENT_USER
USING '<service_name>';
CREATE DATABASE LINK curr_user
CONNECT TO CURRENT_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@curr_user;

-- The user who issues this statement must be a global user 
-- registered with the LDAP directory service.

Fixed User Link
CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING '<service_name>';
CREATE DATABASE LINK fixed_user
CONNECT TO hr IDENTIFIED BY hr
USING 'conn_link';

SELECT * FROM all_db_links;

desc gv$session_connect_info

set linesize 121
set pagesize 60
col authentication_type format a10
col osuser format a25
col network_service_banner format a50 word wrap

SELECT DISTINCT sid
FROM gv$mystat;

SELECT authentication_type, osuser, network_service_banner
FROM gv$session_connect_info
WHERE sid = 143;

SELECT table_name, tablespace_name FROM user_tables@fixed_user;

Shared Link
CREATE SHARED DATABASE LINK <link_name>
AUTHENTICATED BY <schema_name> IDENTIFIED BY <password>
USING '<service_name>';
conn uwclass/uwclass

CREATE SHARED DATABASE LINK shared
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY uwclass IDENTIFIED BY uwclass
USING 'conn_link';

SELECT * FROM user_db_links;

SELECT table_name, tablespace_name FROM user_tables@shared;

Public Link
CREATE PUBLIC DATABASE LINK <link_name>
USING '<service_name>';
conn / as sysdba

CREATE PUBLIC DATABASE LINK
publink
USING 'conn_link';

SELECT * FROM dba_db_links;

conn scott/tiger

SELECT table_name, tablespace_name FROM user_tables@publink;

conn sh/sh

SELECT table_name, tablespace_name FROM user_tables@publink;

conn uwclass/uwclass

SELECT table_name, tablespace_name FROM user_tables@publink;
 
Close Database Link
Close Link ALTER SESSION CLOSE DATABASE LINK <link_name>;
ALTER SESSION CLOSE DATABASE LINK curr_user;
 
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
set linesize 121
col db_link format a45
col username format a15
col password format a15
col host format a15

SELECT db_link, username, password, host, created
FROM user_db_links;

conn / as sysdba

desc link$

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 Connection');
END;
 
Remote PL/SQL
SELECT over a db_link

From Jonathan Lewis's FAQ
Why does it seem that a SELECT over a db_link requires a commit after execution ?

Because it does! When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing. This entry is held until the SQL statement is committed even if the SQL statement is a query.

If the application code fails to issue a commit after the remote or distributed select statement then the rollback segment entry is not released. If the program stays connected to Oracle but goes inactive for a significant period of time (such as a daemon, wait for alert, wait for mailbox entry, etc...) then when Oracle needs to wrap around and reuse the extent, Oracle has to extend the rollback segment because the remote transaction is still holding its extent. This can result in the rollback segments extending to either their maximum extent limit or consuming all free space in the rbs tablespace even where there are no large transactions in the application. When the rollback segment tablespace is created using extendable files then the files can end up growing well beyond any reasonable size necessary to support the transaction load of the database. Developers are often unaware of the need to commit distributed queries and as a result often create distributed applications that cause, experience, or contribute to rollback segment related problems like ORA-01650 (unable to extend rollback). The requirement to commit distributed SQL exists even with automated undo management available with version 9 and newer. If the segment is busy with an uncommitted distributed transaction Oracle will either have to create a new undo segment to hold new transactions or extend an existing one. Eventually undo space could be exhausted, but prior to this it is likely that data would have to be discarded before the undo_retention period has expired.

Note that per the Distributed manual that a remote SQL statement is one that references all its objects at a remote database so that the statement is sent to this site to be processed and only the result is returned to the submitting instance, while a distributed transaction is one that references objects at multiple databases. For the purposes of this FAQ there is no difference, as both need to commit after issuing any form of distributed query.
Executing Remote Procedures <procedure_name>@<database_link>(<parameters>);
exec testproc@remote_db(1);

or

CREATE OR REPLACE SYNONYM testproc FOR remote_db.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.
conn / as sysdba

set linesize 121
col name format a40
col value format a40

SELECT name, value
FROM gv$parameter
WHERE name = 'remote_dependencies_mode';
 
Related Topics
DBMS_DBLINK
DBMS_SESSION
Hints
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [49 users online]    © 2010 psoug.org