Oracle | Database Links

Oracle Database Links Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Notes:

The single quotes around "service name" are mandatory
The service name must be in the TNSNAMES.ORA file on the server

Data Dictionary Objects

ALL_DB_LINKS	DBMS_DBLINK_LIB	LINK$
CDB_DB_LINKS	GV$DBLINK	LINK_LOGONS$
CDB_DB_LINK_SOURCES	GV$SESSION_CONNECT_INFO	LINK_SOURCES$
DBA_DB_LINKS	KU$_DBLINK_T	ORA_KGLR7_DB_LINKS
DBA_DB_LINK_SOURCES	KU$_DBLINK_VIEW	USER_DB_LINKS
DBMS_CLRDBLINK	KU$_TRLINK_VIEW	V_$DBLINK SYS VIEW
DBMS_DBLINK	KU$_10_1_DBLLINK_VIEW

Related Files $ORACLE_HOME/rdbms/admin/caths.sql

System Privileges

ALTER DATABASE LINK	CREATE DATABASE LINK	DROP PUBLIC DATABASE LINK
ALTER PUBLIC DATABASE LINK	CREATE 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'. The db_domain is automatically appended to any database link name.

~ 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.mlib.org;

      

      SELECT *

      FROM props$

      WHERE name LIKE '%GLOBAL%';

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 = perrito5)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = orabase)

          )

        )

      

      conn uwclass/uwclass@pdbdev

      

      CREATE DATABASE LINK conn_user

      USING 'conn_link';

      

      desc user_db_links

      

      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  sid
      FROM v$mystat WHERE rownum = 1;

      

      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@pdbdev

      

      CREATE SHARED DATABASE LINK shared_link

      CONNECT TO scott IDENTIFIED BY tiger

      AUTHENTICATED BY uwclass IDENTIFIED BY uwclass

      USING 'conn_link';

      

      SELECT * FROM user_db_links;

      

      SELECT name FROM sys.link$ WHERE BITAND(flag, 1) = 1;

      

      SELECT table_name, tablespace_name FROM user_tables@shared;

Public Link CREATE PUBLIC DATABASE LINK <link_name> USING '<service_name>';

conn sys@pdbdev 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@pdbdev

      

      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;

Alter Database Link

Change Link Password ALTER [SHARED | PUBLIC] DATABASE LINK <link_name> ....;

CREATE DATABASE LINK fixed_user

      CONNECT TO hr IDENTIFIED BY hr;

      

      col owner format a20

      col db_link format a15

      col username format a20

      col host format a12

      

      SELECT * FROM dba_db_links;

      

      ALTER DATABASE LINK fixed_user

      CONNECT TO hr IDENTIFIED BY hrnew;

      

      SELECT * FROM dba_db_links;

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 sys@pdbdev 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 ALTER SESSION CLOSE DATABASE LINK remove_db; DECLARE i INTEGER; BEGIN 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);

Remote PL/SQL

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}

This 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 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 using 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 sys@pdbdev as sysdba

      

      set linesize 121

      col name format a40

      col value format a40

      

      SELECT name, value

      FROM gv$parameter

      WHERE name = 'remote_dependencies_mode';

Monitor Link Performance

Gathering Database Link Metrics col value format 9999999999.99 col name format a40 SELECT s.statistic#, s.name, s.value FROM v$sysstat s WHERE s.name LIKE '%link%' ORDER BY 1; SELECT sid FROM v$mystat WHERE rownum = 1; SELECT s.statistic#, sn.name, s.value FROM v$sesstat s, v$statname sn WHERE s.statistic# = sn.statistic# AND sn.name LIKE '%link%' AND s.value > 0 AND s.sid = 123 ORDER BY 1;

DB Link Related Queries

Identify SQL Originating at Remote Databases Across DB Links SELECT user_id, program, COUNT(DISTINCT inst_id||session_id||session_serial#) CNXN_CNT FROM gv$active_session_history WHERE program LIKE 'oracle@%TNS%' GROUP BY user_id, program ORDER BY 3 DESC;