The creation and use of Oracle Database Link

Assumption: You need to connect to Db_b from database db_a through Db_link to query database B for part of the relevant information

Prerequisites:

Database a account requires permission to create Dblink if no DBA account can be used to assign permissions

Grant CREATE public DATABASE LINK to username;
Grant DROP public DATABASE LINK to Usenrame;

The database B account needs to have the connection permission, the general account has this permission

The following statements are created:

Basic syntax:

CREATE [shared][public] Database link link_name

[CONNECT to [user][current_user] identified by password]
[Authenticated by the user identified by password]
[USING ' connect_string ']

Instance:

Create DATABASE link A_to_b

Connect to User_b_username identified by User_b_password

Using ' database B address : Port/Service name '

This is a relatively simple way

which

A_to_b is the connection name

User_b_username is a database user who wants a succession of targets

User_b_password is the target database password to connect to

The database cluster can also be written like this.

Create Public database link Dblinkname connect to username identified by password
Using ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = database_ip) (PORT = 1521))
)
(Connect_data =
(Service_Name =servicename)
)
)‘;

Access related information

SELECT * from [email protected]A_to_b

Querying database connections:

SELECT * FROM Dba_db_links

To delete a connection:

Drop DATABASE Link A_to_b

In addition, this method is created by default for this user, if you want to create a common database link

The creation and use of Oracle Database Link