Oracle_Database_Links
Oracle Database Links Oracle Database Link
Version 11.1
General Information
Related Data Dictionary Objects
link$
all_db_links gv_$session_connect_info repcat$_repprop_dbl dba_db_links ku$_dblink_t user_db_links dbms_dblink ku$_dblink_view wmp_api_dblin dbms_dblink_lib ku$_10_1_dblink_view wmp_db_links_ 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 v the GLOBAL_NAMES initialization parameter is TRUE, then the d link must have the same name as the database to which it co
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 a global name is treated as the 'db_name' and the rest of the global_name is treated as the 'db_
~ 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 https://www.wendangku.net/doc/cb17986187.html,;
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
USING '
-- create tnsnames entry for conn_link
conn_link =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1 )
(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_us
Current User Link CREATE [PUBLIC] DATABASE LINK
CONNECT TO CURRENT_USER
USING '
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_us
-- The user who issues this statement must be a global user -- registered with the LDAP directory service.
Fixed User Link CREATE [PUBLIC] DATABASE LINK
IDENTIFIED BY
USING '
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_u
Shared Link CREATE SHARED DATABASE LINK
AUTHENTICATED BY
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
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
Drop Database Link
Drop Standard Link DROP DATABASE LINK
Drop Public Link DROP PUBLIC DATABASE 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 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 rollback segment area for the two-phase commit processing. This entry is held until the SQL sta 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 th rollback segment entry is not released. If the program stays connected to Oracle but goes inact significant period of time (such as a daemon, wait for alert, wait for mailbox entry, etc?) then wh needs to wrap around and reuse the extent, Oracle has to extend the rollback segment because transaction is still holding its extent. This can result in the rollback segments extending to either maximum extent limit or consuming all free space in the rbs tablespace even where there are n transactions in the application. When the rollback segment tablespace is created using extendab the files can end up growing well beyond any reasonable size necessary to support the transac the database. Developers are often unaware of the need to commit distributed queries and as a create distributed applications that cause, experience, or contribute to rollback segment related like ORA-01650 (unable to extend rollback). The requirement to commit distributed SQL exists e automated undo management available with version 9 and newer. If the segment is busy with a uncommitted distributed transaction Oracle will either have to create a new undo segment to ho transactions or extend an existing one. Eventually undo space could be exhausted, but prior to 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
remote database so that the statement is sent to this site to be processed and only the result is the submitting instance, while a distributed transaction is one that references objects at multiple For the purposes of this FAQ there is no difference, as both need to commit after issuing any fo distributed query.
Executing Remote Procedures
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 overridd 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 dyna ?You can alter the mode dynamically by using the DDL statements. For example, this ex the dependency model for the current session:
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMEST
Thise example alters the dependency model systemwide after startup:
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTA
If the REMOTE_DEPENDENCIES_MODE parameter is not specified, either in the init.ora param using the ALTER SESSION or ALTER SYSTEM DDL statements, then timestamp is the default Therefore, unless you explicitly use the REMOTE_DEPENDENCIES_MODE parameter, or the 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 pro calling the remote procedure is not invalidated. If the call to the remote procedure does
the parameter, then the default value is used. In this case, because invalidation/recomp not automatically occur, the old default value is used. If you want to see the new defaul
then you must recompile the calling procedure manually.
?If you add a new overloaded procedure in a package (a new procedure with the same n existing one), then local procedures that call the remote procedure are not invalidated. I that this overloading results in a rebinding of existing calls from the local procedure und timestamp mode, then this rebinding does not happen under the signature mode, becau procedure does not get invalidated. You must recompile the local procedure manually t the new rebinding.
?If the types of parameters of an existing packaged procedure are changed so that the n have the same shape as the old ones, then the local calling procedure is not invalidated recompiled automatically. You must recompile the calling procedure manually to get the of the new type.
Dependency Resolution
When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value), dependencies a program units are handled by comparing timestamps at runtime. If the timestamp of a called rem procedure does not match the timestamp of the called procedure, then the calling (dependent) u invalidated and must be recompiled. In this case, if there is no local PL/SQL compiler, then the application cannot proceed.
In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL co recompilation happens automatically when the calling procedure is run.
When REMOTE_DEPENDENCIES_MODE = SIGNATURE, the recorded timestamp in the calli first compared to the current timestamp in the called remote unit. If they match, then the call pro timestamps do not match, then the signature of the called remote subprogram, as recorded in th subprogram, is compared with the current signature of the called subprogram. If they do not ma the criteria described in the section "When Does a Signature Change?"), then an error is returne 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 tha timestamp dependency mode.
?Server-side PL/SQL users can choose to use the signature dependency mode if they h 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 procedur
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 pr function) declarations in a package specification. Adding a new procedure in the middle declarations can cause unnecessary invalidation and recompilation of dependent proce
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';