Oracle Dblink Configuration

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:

1 Grant CREATE public DATABASE LINK,DROP Public DATABASE LINK to Scott;

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 = 10.10.13.178) (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 : 10.10.13.178

　　ORACLE: rzzqnew

Machine 2:

　　Ip : 10.10.13.35

　　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 = 10.10.13.178)6 (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

　　Perform:

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