Oracle to create a database link in two ways of detailed _oracle

Source: Internet
Author: User
Tags create database

Create a dblink, named Dblink_name, from a database to the B database, the B database IP is 192.168.1.73, the port is 1521, the instance name is Oracle, the login name is tast, and the password is test.

A menu method:

Open Plsql, click "File"-"New"-"Database link" and open the window shown in the following image

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

  Two-SQL Way

--Drop Existing database link
Drop public database link dblink_name;
--Create Database link
Create Public database link Dblink_name connect to SYSTEM using ' 192.168.1.73:1521/oracle ';

These are the two ways Oracle creates database link

The following is a detailed supplement:

The database Linke is an object that establishes a path to another database, and can be used by database link to allow querying of remote tables, which I understand can be counted as a use of distributed databases.

Database link is a one-way connection, and since it is an object, it is natural to query the relevant information in the Xxx_objects table. There are a few things you need to be clear before you set up Database link:

1, confirm from the establishment of the server can access the remote database.
2. You need to configure the remote database connection string in the tnsnames of the establishment.
3, only in the service-side configuration of the connection can be used in Dblink, if only in the client-configured connection can not be used in the Dblink, I have made this error, the implementation of the errors:

SELECT * FROM T@dblinktest
*
ERROR at line 1:
Ora-12154:tns:could not resolve service name

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


Establishment process:

1, the establishment side of the server to configure the remote database Tnsname configuration.
2, tnsping remote database tnsnames configuration name, you can pass.
3, Sqlplus after login 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 tnsnames name in the database server-side configuration.

4. Implementation:

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 built.

5. Implementation:

Sql> select * from T@dblinktest;
Id
----------
0
2
3
4
5

Of course, you can also execute an INSERT statement:

sql> INSERT INTO t@dblinktest values (1);
1 row created.

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

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: info-contact@alibabacloud.com 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.