Oracle creates database Link

One menu way:

Open Plsql, click "File"-"New"-"Database link" to open the window as shown

After filling out the information, click "Apply" to complete the creation of the database link.

　　Two-SQL mode

-- Drop Existing Database link Drop  Public Database link dblink_name; -- Create Database Link Create  Public Database  to ' 192.168.1.73:1521/oracle ';

 --  Create database link  create  database   link db_1 Connect  to  Db_2_user identified by   "Db_2_user_password" using   " demo = (      DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = db_2_ip) (PORT = 1521))) (Connect_data =  (service_name = db_2_server))  ; 

which
Db_1 is the name of the DB link;
Db_2_user is the user name of the source database on the DB2 machine;
Db_2_user_password is the password;
DB_2_IP is the DB2 database address,
Db_2_server is the DB2 database service name.

This is OK, if you want to access the test table of the B database, you can use "Table name @ Data Link name", such as:

Select *  from Test@db_1;

The following is a detailed supplement:

Database Linke is an object that builds a path to another database, which allows querying of remote tables through the DB link, and I understand that it can be counted as a distributed database usage.

Database link is a one-way connection, and since it is an object, it is natural to query the Xxx_objects table for relevant information. There are a few things to be clear before establishing database link:

1. Verify that the server from the established party can access the remote database.
2, need to configure the remote database connection string in the tnsnames of the founding party.
3, only in the server configuration of the connection can be used in Dblink, if only the client configuration of the connection is not used in Dblink, I have made this error here, the execution of errors:

SELECT * from [email protected]
*
ERROR at line 1:
Ora-12154:tns:could not resolve service name

Obviously, Tnsname is not configured on the server side, so the service name configuration of the remote database cannot be found.

Build process:

1, the establishment of the server side configuration remote database Tnsname configuration.
2, tnsping remote database tnsnames configuration name, can pass.
3, Sqlplus login after the execution:

Sql> CREATE DATABASE link dblinktest Connect to user identified by passwd using ' Ceshi ';

which
Dblinktest is the name of the database link.

User is the username of the remote database.
PASSWD is the password for the remote database.
Ceshi is the name in the tnsnames of the database server configuration.

4. Execution:

Sql> Select Owner,object_name from dba_objects where object_type= ' DATABASE LINK ';
OWNER object_name
--------------- -------------------------
OPEN Dblinktest

You can see that the database link has been established.

5. Execution:

Sql> SELECT * from [email protected];
Id
----------
0
2
3
4
5

Of course, you can also execute an INSERT statement:

sql> INSERT into [email protected] VALUES (1);
1 row created.

In addition the database link is divided into public and private, the default is private, only the current user is available, if the Create Public database link ..., then all users can use the name of the database link.

Note: If you access DB2 tables through the database links on DB1 Oracle clinet, you need tnsnames on the DB1 server to correctly point to DB2. Assuming that your db_link is trying to connect from DB1 to DB2, you need to configure tnsnames on the DB1 machine to correctly point to DB2. If you confirm that the tnsnames on the DB1 is properly configured, then confirm that your correct tnsnames is being used, which means that the environment variable of the system on the DB1 machine points to which Oracle's Bin directory, using the TNSNames file that you configured correctly. Two tnsnames the correct configuration is ready to use.

Oracle creates database Link