How to implement real-time table-level synchronization across Oracle databases

How to implement real-time table-level synchronization across Oracle databases
I. Problem Description

Two Oracle databases are deployed on different servers. The system is windows2003;

It is now said to be a primary database and a slave database:

(1) primary database: oracle_A;

(2) slave Database: oracle_ B;

In oracle_A, a table table_A has the same structure as table_ B in oracle_ B;

I am in oracle_ B, And the oracle_A database is assigned to me a user who accesses table _ A of oracle_A. This user has only the query permission;

In addition, it should be noted that at oracle_ B, you only need to query table_ B and do not add, delete, or modify other operations.

After the scenario is introduced, my question is, how can I synchronize real-time updates to table_ B of oracle_ B when table _ A changes in oracle_A?

My original processing method:

By establishing a remote connection to DBLink + JOB scheduled task + stored procedure, you can implement scheduled synchronization and update, but not real-time synchronization.

Ii. Result steps using synonym + DB_Link

You can use synonyms to solve this problem. It mainly comes from a point mentioned in the Problem description, that is, we only need to query the synchronized table. This is an important element of synonym.

The following is a detailed simulation of the entire experiment test process:

(1) first, create a user with only query functions for table_A on Oracle_A.

Create user
Sqlplus/nolog
Conn/as sysdba;
Create user username identified by password;

View the list of all users
After the user is created, check whether the user is successfully created.
Select * from all_users;

Grant Permissions
The following permissions must be granted to Ensure logon.

Grant the username user the permission to create a session, that is, the login permission.
Grant create session to username;

Grant the username user the permission to use the tablespace
Grant unlimited tablespace to username;

Oracle is more rigorous in permission management. Common users cannot access each other by default, and mutual authorization is required.

If scott wants to authorize username users to view their test tables;
Sqlplus scott/tiget @ localhost: 1521/orcl

Authorize the username user to view the specified permissions
Grant select on test to username;

Revoke permissions
The basic syntax is the same as grant. The keyword is revoke;

(2) tables that verify that the user can successfully log on and perform Access Authorization

Use sqlplus to log on and query

sqlplus username/password@localhost:1521/orcl;select * from scott.test;

Note: When querying a table, you must add the user name to indicate which user the table belongs.

(3) create a remote connection to DB_Link and create a remote connection to db_link

    create public database link db32　connect to tianzhi_test identified by "tianzhi_test" using '192.168.56.6:1521/ORCL'

Test whether the remote connection is successful

    select * from tianzhi_smart.zh_item_news@db32;

(4) create a synonym on Oracle_ B and use sqlplus to log on to your user

sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl

Create Synonym

create or replace public synonym TEST1130 for scott.TEST@db32;

3. query Test

select * from TEST1130;

We can see that this is the same as the data in the table_A table found in the Oracle_A source database.
Note:

When the database for remote query contains BLOB fields, the following error is returned.

ORA-22992: unable to use the LOB locator selected from the remote table

When this error occurs, it is because BLOB fields exist in the table in the Cross-database connection query. Therefore, be sure to note that blob fields exist in all tables,

Tables cannot be connected using select * from.
Blob fields cannot appear in scripts.
If the blob fields must be imported, you can create a temporary table before inserting the local table. The method is as follows:

Step 1: create a temporary table

create global temporary table foo ( X BLOB )on commit delete rows;

Step 2 insert a local table

insert into foo select blobcolumn from remoteTable@dl_remote ;