Examples of Oracle using Dblink cross-Library queries

This article describes the Oracle database method for cross-Library queries using Dblink, the Oracle Dblink cross-Library query tutorial, which is required by a friend reference.

Oracle Dblink cross-Library queries

Method One:
First, create a database link:

Copy Code code example:

CREATE Public Database link Data link name CONNECT to login user name identified by password USING ' (DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = IP address of the other Oracle server) (port = port number))
)
(Connect_data =
(service_name = Other Oracle Server service name)
)
)‘

Where the data link name is the service name added to the local Oracle Database Console (Oracle Enterprise Manager Console) tree node
The table tablename statement to query the offset database is as follows:

Copy Code code example:

SELECT field name from [email protected] data link name;

Method Two:
At present most of the database has the need of distributed query. The following is a simple introduction to configuring cross-Library access in Oracle.
For example, there are 2 database servers and 2 databases are installed. Database Server A and B. Now implement the database that accesses B in the a library.
The first step is to configure a server-side Tnsnames.ora file (tnsnames.ora Network configuration file), which is stored in the following location:
$ORACLE _home/network/admin/tnsnames.ora
You need to add a configuration entry to the B library in this file, in the following format

Copy Code code example:

ZBCDB3 =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.1.50.6) (PORT = 1523))
)
(Connect_data =
(service_name = zbcdb3)
)
)

If another client in the a library accesses the database of B, the corresponding files of the client should also be modified.

The second step is to establish a dblink of a data for B in a library of a server. The syntax is as follows: (www.jbxue.com script Academy)
Create DATABASE link Dcmdb connect to Dcmdb identified by Dcmoptr using ' zbcdb3 ';
Distributed queries can then be implemented:
SELECT * from [email protected] where 1=1;

(You can create synonyms for [email protected])

To copy table data:

Copy Code code example:

Insert into table name (field name) (SELECT field name from [email protected] data link name);
--------------------------------------------------------
SELECT * from User_db_links; --User DB Link
SELECT * from Dba_db_links; --DBA DB Link
SELECT * from V$dblink; --Current DB Link
Drop database link MYDBLINK1;

If the same instance, different users, you can use the following methods to query:

Copy Code code example:

SELECT * from user name. Table name

The above is an example of Oracle using Dblink cross-Library query, we hope to help.

Examples of Oracle using Dblink cross-Library queries