Oracle cross-Library query replicates table data

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

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:

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;

Method Two:

First create a database link:
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:
SELECT field name from [email protected] data link name;

To copy table data:

Insert into table name (field name) (SELECT field name from [email protected] data link name);