Oracle Create Dblink

When a user wants to access data from another database table across a local database, the local database must have the dblink of the remote database created, 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 created by Dblink are available, and 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)

)

　　

Create Database link Mydblink Connect to MANAGER identified by password using ' ORCL ';

Dblink after the creation is complete

Two databases exist Tb_demo table This is the table inserted
Querying data SQL is the same as local, just adding a @ddd.regress.rdbms.dev.us.oracle.com to the back is the dblink of a good remote database.

INSERT into tb_demo SELECT * from [email protected]mydblink

If there is no corresponding table, you can

CREATE TABLE Tb_demo as SELECT * from [email protected]Mydblink

Oracle Create Dblink