Two methods for creating dblink in Oracle

When you want to access data in another database table across local Oracle databases, dblink of the remote database must be created in the local database.

When you want to access data in another database table across local Oracle databases, dblink of the remote database must be created in the local database.

When you want to access data in another database table across local Oracle databases, dblink of the remote database must be created in the local database, dblink allows you to access data in a remote database table just like a local database. The following describes how to create a dblink in a local database.

You can create a dblink in two ways. However, you must have the permission to create a dblink before creating a dblink. To learn about dblink permissions, log on to the local database as a sys User:

Select * from user_sys_privs t
Where t. privilege like upper ('% link % ');
1 SYS CREATE DATABASE LINK NO
2 SYS DROP PUBLIC DATABASE LINK NO
3 SYS CREATE PUBLIC DATABASE LINK NO

It can be seen that in the DATABASE, dblink has three permissions: create database link (the created dblink can only be used by the creator, but cannot be used by other users ), create public database link (public indicates that all users of the created dblink can use it) and drop public database link.

Grant the create public database link, and drop public database link permissions to your users.

Grant create public database link, drop public database link to scott;

Then log on to the local database as scott.

1. The first way to create dblink is to configure the database to be remotely accessed in the tnsnames. ora file of the local database.

Create public database link

To_bylw connect to scott identified by tiger using 'bylw ';

Here, to_bylw is the name of the dblink you created, bylw is the Instance name of the remote database, and scott/tiger is the user/password used to log on to the remote database. Then access the scott. tb_test table in the remote database 'bylw' through dblink in the local database. The SQL statement is as follows:

Select * from scott. tb_test @ to_bylw;

2. The second method for creating a dblink is that the remote database to be accessed is not configured in the tnsnames. ora file of the local database.

Create database link to_test
Connect to scott identified by tiger
Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.5) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bylw)
)
)';

The second is to put the first information configured in the tnsnames. ora file directly after the dblink statement is created. In the first case, the information in the tnsnames. ora file is as follows:

Bylw =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.5) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bylw)
)
)

Recommended reading:

Use Oracle DBLink for table Synchronization

Use Oracle DBLink

Oracle accesses GreenPlum through DBLink

ORA-01017/ORA-02063 DBLink build Error Problem Analysis and Solution

Oracle create DBLink error: ORA-01017, ORA-02063