Materialized View for two-way synchronization of Oracle database tables

There are many ways to synchronize tables between Oracle databases, such as triggers, MaterializedView (MV), Stream, and Goldengate. MaterializedVi

There are many ways to synchronize tables between Oracle databases, such as triggers, Materialized View (MV), Stream, and Goldengate. Materialized Vi

There are many ways to synchronize tables between Oracle databases, such as triggers, Materialized View (MV), Stream, and Goldengate.

Materialized View is a database object that includes query results. It is a local copy of remote data or used to generate a summary table based on the sum of data tables. materialized views Store Data Based on Remote tables, also known as snapshots. this basically represents the essence of the materialized view, which is the result of a group of queries. This will inevitably greatly improve the query performance when this set of data is required again in the future. the following describes how to use Materialized View + Job to synchronize tables in two directions. The specific steps are as follows:

1. CREATE table on source database A and target database B respectively

Create table test
(
Id varchar2 (10) not null primary key,
Name varchar2 (20 ),
Status varchar2 (1 ),
Updatedate date
)

2. Create dblink on the database separately

Create database link dblink_to_ B
Connect to "userid" identified by "password"
Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = "ipaddress") (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = "SID ")
)
)';

Create database link dblink_to_A
Connect to "userid" identified by "password"
Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = "ipaddress") (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = "SID ")
)
)';

3. Create Materialized View and Materialized view log on source database.

Create materialized view log on test with rowid

Create materialized view mv_test refresh fast on demand with rowid
As select * from test @ dblink_to_ B

,