Oracle Database Links Survival Guide

Martin Zahn, 06.02.2009

More Information can be found in the Oracle Distributed Database Management Guide

Overview

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites

  • You must have the CREATE DATABASE LINK system privilege.
  • You must have the CREATE SESSION system privilege on the remote Oracle database.
  • You must be able to connect to the remote database.
  • Check GLOBAL_NAMES - to setup the name of the database link.

DB_NAME

The DB_NAME parameter is the SID of the database and is the name used when creating the database. It is specified within the INIT.ORA parameter file or in the CREATE DATABASE command.

select name, value from v$parameter where name = 'db_name';

DB_DOMAN

The DB_DOMAIN parameter is the value of the domain to which the database belongs. It can be the same as defined in the DNS - and is usually the company name. However you can set a DB_DOMAIN value which is not part of the DNS.

select name, value from v$parameter where name = 'db_domain';

GLOBAL_NAMES

GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are connecting. Usually GLOBAL_NAMES is set to TRUE.

select name, value from v$parameter where name = 'global_names';

Setting the Name of the Database Link

You can query GLOBAL_NAME on the remote database to find out how the name of the database name has to be on the local database.

CELLAR> sqlplus system/...@GENTIC

SQL> select global_name from global_name;

 GENTIC.COMPANY.COM

So you have to set the database link name to GENTIC.COMPANY.COM if GLOBAL_NAMES is TRUE.

SQLNET

Setup your TNSNAMES.ORA so you can connect to the remote database.

GENTIC =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = gentic.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GENTIC.COMPANY.COM)
    )
  )

CELLAR =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = cellar.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CELLAR.COMPANY.COM)
    )
  )

Refer to Remote Table(s)

Suppose you want to refer to the table scott.aTable on GENTIC from CELLAR. On CELLAR the same name scott.aTable should be used - so you don't have to change any code.

sqlplus system/....@CELLAR

SQL> CREATE DATABASE LINK GENTIC.COMPANY.COM
     CONNECT TO scott IDENTIFIED BY .....
     USING 'GENTIC';

Database link created.

SQL> DESC scott.aTable@GENTIC.COMPANY.COM;

Name                                      Null?    Type
----------------------------------------- -------- --------------
 LANGUAGE                                  NOT NULL VARCHAR2(2)
ACTIVE                                    NOT NULL NUMBER
TEXT                                               VARCHAR2(20)

SQL> SELECT * FROM scott.aTable@GENTIC.COMPANY.COM;

no rows selected

SQL> CREATE SYNONYM scott.aTable FOR scott.aTable@GENTIC.COMPANY.COM;

Synonym created.

SQL> SELECT * FROM scott.aTable;

no rows selected

SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('de',1,'german');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('en',1,'english');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('fr',1,'french');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('it',1,'italian');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('ru',0,'russian');
SQL> COMMIT;

SQL> SELECT * FROM scott.aTable;

LANGUA     ACTIVE TEXT
------ ---------- -------------------------
de              1 german
en              1 english
fr              1 french
it              1 italian
ru              0 russian

As you can see, accessing the remote table is fully transparent to the application. Exactly the same syntax can be used for the remote table if you create a synonym for the remote table.

Executing Remote Procedures

You can also execute remote procedures ...

... here an example without synonym.

sqlplus system/....@CELLAR

SQL> DESC scott.aPackage@GENTIC.COMPANY.COM;

 PROCEDURE aProcedure

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 USER_ID                        NUMBER(10)              IN
 A_NAME                         VARCHAR2(32)            IN
 A_CONTENT                      VARCHAR2(4000)          IN
 A_DEFAULT                      NUMBER(1)               IN

SQL> EXECUTE scott.aPackage.aProcedure@GENTIC.COMPANY.COM(1,'Test','Test',0);

PL/SQL procedure successfully completed.

And now the same with synonyms.

SQL> CREATE SYNONYM scott.aPackage for scott.aPackage@GENTIC.COMPANY.COM;

Synonym created.

SQL> EXECUTE scott.aPackage.aProcedure(1,'Test','Test',0);

PL/SQL procedure successfully completed.

As you can see, executing remote procedures is fully transparent to the application. Exactly the same syntax can be used for the remote package if you create a synonym for the remote package.

Distributed / In-Doubt Transactions

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.

The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if the transaction fails because of a system or network error? The transaction becomes in-doubt.

Distributed transactions can become in-doubt in the following ways:

  • A server machine running Oracle software crashes.
  • A network connection between two or more Oracle databases involved in distributed processing is disconnected.
  • An unhandled software error occurs.

The RECO process automatically resolves in-doubt transactions when the machine, network, or software problem is resolved. Until RECO can resolve the transaction, the data is locked for both reads and writes. Oracle blocks reads because it cannot determine which version of the data to display for a query.

Manually Committing an In-Doubt Transaction

Suppose, the database  server GENTIC crashes during a distributed transaction.

Prepare distributed transaction

SQL> CREATE SYNONYM scott.aTable FOR scott.aTable@GENTIC.COMPANY.COM;
SQL> SELECT * from scott.aTable;
SQL> CREATE TABLE scott.bTable (count NUMBER);

Start the distributed transaction

SQL> INSERT INTO scott.bTable (count) values (1);
SQL> INSERT INTO scott.bTable (count) values (2);
SQL> INSERT INTO scott.bTable (count) values (3);
SQL> INSERT INTO scott.bTable (count) values (4);
SQL> INSERT INTO scott.bTable (count) values (5);
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('de',1,'german');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('en',1,'english');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('fr',1,'french');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('it',1,'italian');
SQL> INSERT INTO scott.aTable(language, active, text) VALUES ('ru',0,'russian');
SQL> UPDATE scott.bTable SET count = 2 WHERE count = 1;

Now shutdown the distributed database

GENTIC> sqlplus / as sysdba;
shutdown abort;

Now try to COMMIT the distributed transaction

SQL> commit;

ERROR at line 1:
ORA-02054: transaction 1.37.5070 in-doubt
ORA-02068: following severe error from GENTIC
ORA-03113: end-of-file on communication channel

Now check the DBA_2PC_PENDING view for the LOCAL_TRAN_ID.

SET LINESIZE 500;
COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A50
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
   FROM DBA_2PC_PENDING
/

LOCAL_TRAN_ID GLOBAL_TRAN_ID                        STATE    MIX HOST       COMMIT#
------------- ------------------------------------- -------- --- ---------- ---------
1.37.5070     CELLAR.COMPANY.COM.a05b1b48.1.37.5070 prepared no  cellar     15508150

Now manually commit or rollback force the in-doubt transaction as user SYS

SQL> exit;

sqlplus / as sysdba

SQL> COMMIT FORCE '1.37.5070';

Commit complete.

Manually remove an entry from the data dictionary

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.37.5070');

PL/SQL procedure successfully completed.

More Information to Distributed Transactions can be found here

Database Link Types

When you create a database link, you must decide who will have access to it. The following sections describe how to create the basic types of links.

Following are examples of private database links

SQL Statement

 Result

CREATE DATABASE LINK link_name;

 A private link using the global database name to the remote database.

The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE DATABASE LINK link_name
 CONNECT TO user IDENTIFIED BY ...
USING 'service';

 A private fixed user link to the database with service name service. The link connects to the remote database with the userid/password regardless of the connected user.

CREATE DATABASE LINK link_name
 CONNECT TO CURRENT_USER USING 'service';

 A private link to the database with service name service. The link uses the userid/password of the current user to log onto the remote database.

Following are examples of public database links

sdasd
SQL Statement Result
CREATE PUBLIC DATABASE LINK link_name; A public link to the remote database. The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.
CREATE PUBLIC DATABASE LINK link_name
 CONNECT TO CURRENT_USER USING 'service';		 A public link to the database with service name service. The link uses the userid/password of the current user to log onto the remote database.
CREATE PUBLIC DATABASE LINK link_name
 CONNECT TO user IDENTIFIED BY ....;		 A public fixed user link. The link connects to the remote database with the userid/password.