Oracle cross-database query replication table data distributed query introduction, oracle

Oracle cross-database query replication table data distributed query introduction, oracle

Method 1:

Currently, most databases require distributed queries. The following describes how to configure cross-database access in oracle.
For example, there are two database servers and two databases are installed. Database server A and B. Now, database A accesses database B.

Step 1: configure the tnsnames. ora File (TNSNAMES. ORA Network Configuration File) on server A. The File is stored:

$ ORACLE_HOME/network/admin/tnsnames. ora

Add the following lines, where DBLINK is the connection name (customizable), HOST and PORT are the IP address and PORT for database listening, and SERVICE_NAME is the SID of the database,

Copy codeThe Code is as follows:
MEDIADBLINK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = db)
)
)

Step 2: create A data DBLINK for B in A database of server.

Syntax:

Execute the following query statement. MEDIADB is the database link name (customizable), and MEDIADBLINK is the connection name defined in tnsnames. ora,
Dbuser is the user name and password

Copy codeThe Code is as follows:
-- Create database link
Create database link MEDIADB
Connect to dbuser identified by password
Using 'mediadblink ';

Step 3. Use the connected database

3.1 Data Query, deletion, and insertion are the same as local databases, except that the table name must be written as "table name @ database link name", as shown in figure
 
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 that of the 3.1 statement.
 
Select * from aaa;
 
The statement for deleting synonyms is
 
Drop synonym aaa;

Select * from tabname @ dcmdb where 1 = 1;

Method 2:

First, create a database link:

Copy codeThe Code is as follows:
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 = service name of the Oracle server of the other party)
)
)'

The Data Link name is the service name of the Tree node added to the local Oracle Database Console (Oracle Enterprise Manager Console ).
The TableName statement is as follows:
SELECT field name FROM TableName @ Data Link name;

Copy table data:

Insert into Table Name (field name) (SELECT field name FROM TableName @ Data Link name );

View DBLINK:

Select owner, db_link from dba_db_links;

Delete:

Drop database link dblink name
Drop public database link dblink name

Oracle password problems:

SQL> CREATE USER AAA IDENTIFIED BY 1;
Create user aaa identified by 1
*
ERROR is located in row 1st:
ORA-00988: Missing or invalid password

SQL> CREATE USER AAA IDENTIFIED BY "1 ";

User Created

How Does oracle copy tables and table data?

Create table T_BAK as select * FROM T

Oracle 10 Gb how to configure distributed databases and distributed queries how to partition data tables

Search for some information. Baidu is enough. Just take a look.
1. shared disk System of MPP Architecture
2. Host Cluster technology and line technology
3. Oracle RAC Architecture
4. Oracle Data Table Partitioning technology
5. The parallel query technology of Oracle and Its Relationship with data table partitions