oracle database link

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