Oracle Faq

HI,

Many times it is necessary for the two or more databases to talk to each other so that they can share few tables or in general data between each other.

In oracle the two database can communicate with each other by means of database objects called as database link or also called as DB LINKS. A database link is an object in the
local database that allows you to access objects on a remote database or to mount a secondary database in read-only mode. The remote database can be either an Oracle or a non-Oracle database.

The syntax to create a DB LINK is as shown

CREATE [PUBLIC] DATABASE LINK dblink
[CONNECT TO user IDENTIFIED BY password]
[USING ‘connect_string’]

where:

PUBLIC
creates a public database link available to all users. If you omit
this option, the database link is private and is available only to
you.

dblink
is the complete or partial name of the database link. For
guidelines for naming database links, see the section Referring to
Objects in Remote Databases.

CONNECT TO user
IDENTIFIED BY password
is the username and password used to connect to the remote database.
If you omit this clause, the database link uses the username and
password of each user who uses the database link.

USING
specifies either:

* the database specification of a remote database
* the specification of a secondary database for a read-only
mount.

For information on specifying remote databases, see the SQL*Net
User’s Guide for your specific SQL*Net protocol.

Read-only mounts are only available in Trusted Oracle and can only
be specified for public database links.

PREREQUISITES:
To create a private database link, you must have CREATE DATABASE
LINK system privilege. To create a public database link, you must
have CREATE PUBLIC DATABASE LINK system privilege. Also, you must
have CREATE SESSION privilege on a remote database. SQL*Net must be
installed on both the local and remote databases.

For e.g

create public database link ITALIC2UTS
connect to utsav identified by utsav
using ‘UTS’;

Once the DB LINK is Created you can verify the it from the data dictionary view DBA_DB_LINKS.

SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
——- ———- ———- ———- —————
PUBLIC ITALIC2UTS UTSAV uts 22-JUN-07

To view the tables from other database under the schema utsav we can use the following query.

SQL> select * from emp@italic2uts;

EMPID
———-
1
2
3
4

Common Errors while creating a DB LINKS are

ORA-02019: “connection description for remote database not found”

Cause: The user attempted to connect or log in to a remote
database using a connection description that could not
be found.

This error can be reported for the following reasons:

1. You are trying to use a database link created by another user as a private
database link. If the ‘PUBLIC’ option is not specified in the create
database link statement, the database link will be created as a private
database link and will only be accessible to the user who created it.

2. You are trying to use a database link that was created without a connect
string. Although you will not receive an error when creating the database
link, at the moment you try to use the newly created database link you will
receive the error.

3. You are trying to use a database link that does not exist.

Another Common error is

ORA-2085 “database link %s connects to %s”

Created a new database link pointing to a schema in another database. The db-link creation went through successfully.
SQL statement using the db-link is returning error ORA-2085

Cause
GLOBAL_NAMES is set to true in both instances.
The instance where the link is being created has a domain name specified.
The target instance does not.

IMPORTANT NOTE: “If there is no domain in the target’s global_name table, the domain would be derived from the source database’s global_name table.”

Solution
Alter the global_name of the remote instance to match what is expected of from the local instance.
SQL> alter database rename global_name to dbname.domain;

OR

If GLOBAL_NAMES is not required, set this parameter to FALSE.

References
Note 210630.1 - Database Links: Troubleshooting ORA-2085 “database link %s connects to %s”

Share This