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

Oracle cross-database query replication table data distributed query, 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 1Configure 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, MEDIADBLINK = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = db )))

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

Execute the following query statement. MEDIADB is the database link name (which can be customized), and MEDIADBLINK is prior to tnsnames. the connection name defined in ora. dbuser is the user name and password is the password -- Create database link create database link MEDIADB connect to dbuser identified by password using 'mediadblink ';

Step 3. Use the connected database3.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", for example, select * from table_name @ MEDIADB; 3.2 you can also create a synonym "create synonym aaa for table_name @ MEDIADB" for this table. The following statement returns the same effect as select * from aaa in 3.1. The statement for deleting a synonym is drop synonym aaa;

Select * from tabname @ dcmdb where 1 = 1;

Method 2:

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 = 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

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