Oracle cross-Library query replication table data distributed query Introduction _oracle

Method One:

At present, most databases have the need of distributed query. The following is a brief description of how to configure implementing Cross library access in Oracle.
For example, there are now 2 database servers, 2 databases installed. Database Server A and B. Now to implement the database that accesses B in library A.

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), and the host and port are the IP and ports that are listening for the database, Service_Name is the SID of the database.

Copy Code code as follows:

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 the dblink of a data for B in a library of server A.

The syntax is as follows:

Execute the following query, where MEDIADB is the database link name (customizable) Mediadblink the connection name previously defined in Tnsnames.ora.
Dbuser for username, password for password

Copy Code code as follows:

--Create Database link
Create DATABASE link mediadb
Connect to Dbuser identified by password
Using ' Mediadblink ';

The third step. Using linked Databases

3.1 Querying, deleting, and inserting data is the same as manipulating a local database, except that the table name needs to be written as "table name @database link Name", as

SELECT * from TABLE_NAME@MEDIADB;

3.2 You can also create a synonym for this table

Create synonym AAA for table_name@mediadb;

The effect of the following statement is the same as in 3.1

SELECT * from AAA;

The statement that deletes the synonym is

Drop synonym AAA;

SELECT * from Tabname@dcmdb where 1=1;

Method Two:

First create a database link:

Copy Code code as follows:

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

Where the name of the data chain is the service name added to the local Oracle Database Console (Oracle Enterprise Manager Console) tree node
The table tablename statement to query the other database is as follows:
SELECT field name from tablename@ data chain name;

Copy table data:

Insert into table name (field name) (SELECT field name from tablename@ data chain name);

View Dblink:

Select owner, db_link from Dba_db_links;

Delete:

Drop DATABASE Link Dblink name
Drop public Database link Dblink name

Problem with Oracle Password:

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

Sql> CREATE USER AAA identified by "1";

User has created