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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.