Oracle cross-Library query replication table data distributed query

Method One:

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

Add the following line, where Dblink is the connection name (customizable), host and port for the database listening IP and ports, service_name for the database sid, Mediadblink = (DESCRIPTION = (Address_list =     (ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521)) (Connect_data = (service_name = db)))

The second step is to establish a dblink of a data for B in a library of a server. The syntax is as follows:

Execute the following query, where MEDIADB is the database link name (customizable), Mediadblink is the connection name previously defined in Tnsnames.ora, Dbuser is the user name, password is the password-Create datab ASE Link  CREATE DATABASE link mediadbconnect to Dbuser identified by passwordusing ' mediadblink ';

step three. Using a linked database3.1 Querying, deleting, and inserting data and manipulating the local database is the same, except that the table name needs to be written as "table name @database link Name", as  SELECT * from [email protected];3.2 You can also create a synonym for this table creating synonym AAA for [email protected];    The effect of the following statement is the same as in 3.1 select * from AAA; Delete the synonym statement for the drop synonym AAA;

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);

View DBLINK:

Select owner, db_link from Dba_db_links;

Delete:

Drop DATABASE Link Dblink name

Problem with Oracle Password:

sql> CREATE USER AAA identified by 1;
CREATE USER AAA identified by 1
                              *
ERROR on line 1th:
ORA-00988: Missing or invalid password

sql> CREATE USER AAA identified by"1";

User has created

Oracle cross-Library query replication table data distributed query