How to Create a database link in Oracle

Source: Internet
Author: User


Assume that there are two database servers, db_a and db_ B,
When user user_a under db_a needs to use database link to access data of user_ B under db_ B, perform the following steps:

1. First, the user user_ B must have the select permission.

2. Create a connection string tnsname_btoa in tnsnames. ora on db_ B. You can connect to db_a from db_ B.

3. Create a connection string tnsname_atob in tnsnames. ora on db_a. You can connect to db_ B from db_a.

4. Create a DB link after logging on to db_a. The format is as follows:

Create database link link_atob_name connect to user_ B identified by user_ B's password using 'tnsname _ atob ';

Link_atob_name must be the global name (SID) of the remote database ),
Tnsname_atob is the remote connection string.

5. Test whether data can be read.

Select * from dual @ link_atob_name

Indicates that the setting is successful.

6. troubleshooting:
(1) ORA-02085:
You must use the global name (SID): global_name of the remote database of db_ B as the Link name.
The global name (SID) of the remote database can be obtained by logging on to the remote machine and executing the following SQL statement.
Select * From global_name;

7. Delete the database link:
Drop database link link_name.

8. How do I query whether a database link exists?
Select owner, db_link, username, host, created from all_db_links

You can also use select owner #, name, ctime, host, userid, password from SYS. Link $ to query

Note: After executing the statement for creating a DB link in PLSQL, the password is automatically deleted (identified by ***), so you don't have to worry about writing the password in the statement.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.