Oracle cross-origin access to other instance data in dblink Mode

To access data in another database table across local databases, you must create a remote database dblink in the local database.

To access data in another database table across local databases, you must create a remote database dblink in the local database.

When you want to access data in another database table across local 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 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)

)

)

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

-------------------------------------- Split line --------------------------------------