How to Create a database link in Oracle

 

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

Result:
Dummy
X
Indicates that the setting is successful.

6. troubleshooting:
(1) ORA-02085:
Solution:
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.