Oracle Create Dblink method

When a user wants to access data in another database table across a local database, the local database must have a dblink of the remote database, and the Dblink local database can access the data in the Remote database table as if it were accessing the local database. The following describes how to create a dblink in a local database.

There are generally two ways to create Dblink, but users must have permission to create Dblink before creating Dblink. To know the permissions for Dblink, log in to the local database with the 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

You can see that there are three kinds of permissions in the database Dblink CREATE Database LINK (the created Dblink can only be used by the creator, other users will not be able to use), create public database Link (public means that all users of the created Dblink are available), DROP public DATABASE link.

Under the SYS user, grant the Create public database Link,drop public database LINK permission to your users

Grant CREATE Public Database Link,drop publicly database LINK to Scott;

Then log in to the local database with the Scott user

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

Create Public Database link

TO_BYLW connect to Scott identified by Tiger using ' bylw ';

Where TO_BYLW is the Dblink name you created, BYLW is the instance name of the remote database, and Scott/tiger is the user/password to log in to the remote database. The Scott.tb_test table in the remote database ' BYLW ' is then accessed through Dblink in the local database, as shown in the SQL statement

SELECT * from [email protected]_bylw;

2. The second way to create Dblink is to not configure the remote database to be accessed in the local database Tnsnames.ora file.

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 that is configured in the Tnsnames.ora file directly behind the Create DBLINK statement. 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)

)

)

Oracle Create Dblink method