Oracle Dblink Configuration

Source: Internet
Author: User

I. DBlink Brief Introduction

  dblink Database link as the name implies is a database link , like a telephone line, is a channel, when we want to cross the local database, access to another database table data, the local database must be created in the remote database Dblink, dblink Local databases can access data from remote database tables as if they were local databases.  

two. Create dblink


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:

1 Select *  from User_sys_privs T 2 where  like Upper ('%link%');

Query result set:


You can see that there are three kinds of permissions in the database Dblink:

1.CREATE DATABASE LINK (the created Dblink can only be used by the creator, other users will not be able to use),

2.CREATE public means that all users of the created Dblink are available.

3.DROP public DATABASE LINK. (Delete dblink)
Under the SYS user, grant the Create public DATABASE link,drop public Databaselink permissions to your users:


Then log in to the local database with the Scott user

2.1 Local services already configured 
1 Create  Public Database Link Link_name 2  to  by Password 3 ' connect_string ';

Note : Link_name is a connection name that can be customized;

Username is the user name of the login database;

Password is the user password of the login database;

Connect_string is a database connection string.

The database connection string is the alias name defined in the Tnsnames.ora file in the current client database. You can use NET8 Easy config or directly modify the Tnsnames.ora definition. 

2.2 Direct Link Creation

1 Create DatabaseLink Link_name2Connect toUsername identified byPassword3Using'(DESCRIPTION =4 (address_list =5 (ADDRESS = (PROTOCOL = TCP) (HOST = (PORT = 1521))6     )7 (Connect_data =8 (service_name = rzzqnew)9     )Ten   )';

Note : The IP address of the host= database;

Service_name= the SSID of the database.

2.3 Comparison of two methods

  In fact, the two methods configured Dblink are similar, the second method is not affected by the local service.
   Note : If you create a global dblink, you must use the SYSTM or SYS user to add public before database.

three. Dblink Common Commands

3.1 dblink Query

To view all the database links, go to the system administrator sql> operator and run the command:

1 SQL>Select owner,object_namefrom2where object_type='DATABASE LINK'; 3 or 4 Select *  from Dba_db_links;
3.2 Dblink Delete
1 DROP  Public DATABASE LINK Link_name;
3.3 dblink use  

  SELECT ... From table name @ database link name; 

Querying, deleting, and inserting data is the same as manipulating the local database, except that the table name needs to be written as a "table name @dblink Server".

Four. Configuring Instances

4.1 Machine Preparation

Machine 1:

  Ip :

  ORACLE: rzzqnew

Machine 2:

  Ip :

  ORACLE: headdb

4.2 Creating a DB link

  Login HEADDB database, account Kingstar password Kingstar


1 Create  DatabaseLink Dblinktest2Connect toKingstar identified byKingstar3Using'(DESCRIPTION =4 (address_list =5 (ADDRESS = (PROTOCOL = TCP) (HOST = (PORT = 1521))7                 )8 (Connect_data =9 (service_name = rzzqnew)Ten                 ) One              ) ';

Note : 1. When creating Dblink, it is important to see if the local global_name is true, and if the local global_name =true, then the dblink you create must be the same as the global_name of the remote database, otherwise create Dblink Nor can the data be found;

2. The above code, if the wrong, the use of Dblink will be an error.

The Dblink name we created here is dblinktest, not the same, so you can make the following changes.

1 Alter  System  set  global_names=false;
4.3 using Dblink


1  Select *  from dual@dblinktest;

The results are as follows:

This approach enables the use of content under the Rzzqnew database on the HEADDB database.

Note : In fact, the two methods configuration Dblink is similar, the second method is not affected by local services;
If you create a global dblink, you must use the SYSTM or SYS user to add public before database.


Oracle Dblink Configuration

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.