Oracle 10g enables table-level data synchronization across different databases by creating materialized views

Excerpt from: http://blog.csdn.net/javaee_sunny/article/details/53439980

Directory (?) [-]

1. The Oracle 10g materialized view syntax is as follows
2. Example Demo
1. Main steps
1. Creating the original table and materialized view logs on a node
2. Create a remote link to a node on the B node
3. Create the target table at the B node and the same materialized view as the target table name
4. Refresh materialized view at B node
5. Upgrade a scheduled task job to refresh materialized views on a timed basis with a stored procedure
6. Further optimization
2. Article Update record
3. Reference articles

The Oracle 10g materialized view syntax is as follows:

Create View [view_name]  [fast|complete|force][on[Commit|demand] |  withnext  (next_time)]as {Create a query statement for materialized views}

These are the common syntax for Oracle to create materialized views (materialized view, MV), and the meanings of each parameter are as follows:

2. Refresh [Fast|complete|force] View:

   fast: Incremental refresh. Assuming that the time of the previous refresh is T1, when you refresh the materialized view using fast mode, only the data that has changed from the primary table to the current time period is added to the view T1.      to record this change, a materialized view Log table is also required to establish an incremental refresh materialized view.       Create View Log  on (the primary table name). (This statement also takes effect when multiple tables are created, and the original table will have two more types of view charts: Mlog$_table_name and Rupd$_table_name) Complete: Refresh all. Equivalent to re-executing the query statement that created the view. Force: This is the default data refresh method. When you can use fast mode, the data refresh will be in fast mode, otherwise use the complete method. 

   2.MV Data Refresh Time:

3. n Demand: Refresh when the user needs to refresh, here requires the user to manually refresh the data (can use the job timed refresh);  on Commit : When data is submitted in the main table, the data in the MV is immediately refreshed; : Starts at a specified time, refreshes once at a time (specified by next); Fully refreshed mv_test materialized view:

   begin      Dbms_mview.refresh (TAB='mv_test',                        METHOD = '  Complete ' ,                        PARALLELISM=8); End ; /

   Incremental refresh:

begin      Dbms_mview.refresh (TAB='mv_test',                        METHOD = ' FAST ' ,                        PARALLELISM=1); End ; /

Alternatively, you can do this:

 exec  dbms_mview.refresh ( " mv_test   ",   f   " ) ;d Bms_mview.refresh (  "  table name   ", "  f  " ) --  Fast refresh, that is, incremental refresh  Dbms_mview.refresh ( "  table name   ", "  c   "" --  full flush  

Example Demo:

Here is a process record for me to create a materialized view that implements data synchronization at the table level between different databases (two servers A and B, where data is synchronized from table Zh_major_item of Node A to Node B). The process of synchronizing now is still a one-way process, that is, a node-b node:

Main steps:

1. Creating the original table and materialized view logs on a node
2. Create a remote link to a node on the B node
3. At the b node, create the target table and the materialized view with the same name as the target table

1. Create the original table and materialized view logs on the a node:

-- create materialized view log at source a sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/ ORCL -- The original table Zh_major_item has existed before, this does not repeat the table creation process -- Create materialized view logs Create View Log  on Zh_major_item;

2. Create a remote link to the connection a node in the B node:

--Create a remote connection at Target B--If you do not have permission to create a remote connection, you need to login to SYSDBA to authorize the user;--It's the same as above because my username and password are the same.Sqlplus Tianzhi_smart/Tianzhi_smart@localhost:1521/ORCL--Create a remote connection Db_linkCreate  Public Databaselink db_link_a Connect toTianzhi_smart identified by"Tianzhi_smart" using'192.168.56.6:1521/ORCL';--Verify that the creation is successfulSelect *  fromZh_major_item@db_link_A;

3. At the b node, create the target table and the materialized view with the same name as the target table:

--Create the target table at Node B bc_major_itemCreate TableBc_major_item as Select *  fromZh_major_item@db_link_A where 1=2;--create a materialized view with the same name as the target table (Bc_major_item) with a remote connection to the a node--register a new materialized view with on prebuilt table, note that the view name must be the same as the table name--materialized views created using on prebuilt table are deleted and the original tables are not deleted--Note Here I create a materialized view of the refresh fast on demand type CreateMaterializedViewBc_major_item onprebuiltTableRefresh Fast onDemand as Select *  fromZh_major_item@db_link_A;

4. At the b node, refresh the materialized view

-- Refresh materialized views exec Dbms_mview.refresh (','C'   -- after the refresh, query the target table, compared to whether the same as the original table data Select* from Bc_major_item;

5. Upgrade with stored procedure + timed task job, refresh materialized view periodically

 --create a stored procedure for incremental refreshes    Create or Replace procedureRefresh_bc_major_item as    beginDbms_mview.refresh ('Bc_major_item','F'); End; /    --Create Task JobSql>Variable Job1 Number; SQL>beginDbms_job.submit (: Job1,'Refresh_bc_major_item;', Sysdate,'sysdate+1/(86400)'); End; /    --Run JobSql> beginDbms_job.run (: JOB1); End; /

6. Further optimization:

You can write a stored procedure + scheduled task job, and periodically empty the materialized view log.

Article Update record

v1.0– surface is the realization of synchronization, in fact, the query is only the view, and did not complete as the topic of the problem referred to
v2.0– The goal table and materialized view to establish the connection, really realizes the data synchronization. 2016.12.6

Reference article:

1. Oracle 11g enables table data synchronization between different databases by creating materialized views
2. Synchronizing tables with materialized views in Oracle

============================================================================ elder brother Guidance: (1) The pre-built table has to add the primary key, Pre-built tables when creating materialized views add rowID will error, using the pre-built table can not be used with ROWID pre-built table limitations large, can only be used with the primary key, can not be used with ROWID pre-built table: is to create a table in advance to store the materialized view Table of Figure data (2) Creating materialized view statements (modifiable materialized views)

 create  materialized view  log  on  batch_step_execution with   ROWID;  create  Materialized view   mv_batch_step_execution Tablespace etlnologging COMPRESS  for   Oltprefresh FAST  with  ROWID for  Span style= "COLOR: #0000ff" >update  enable QUERY REWRITE  as  select  *  from  batch_step_execution @ETL_55_AP  cu 

(3)

Login hr User Rain hin, on prebuilt table clause allows us to register existing heap tables as pre-initialized materialized views.

This design is mostly in a few silos, materialized views are deleted and can be restored by pre-built tables

With reduced precision is similar to authorization, if the accuracy of the table or materialized view column does not match the precision returned by the subquery, it will cause the precision to be missing and give the keyword a control

Oracle 10g enables table-level data synchronization across different databases by creating materialized views