Oracle materialized view, materialized view log, incremental refresh synchronization remote database, oracle materialized

Oracle materialized view, materialized view log, incremental refresh synchronization remote database, oracle materialized

1. Create DBLINK

 -- Drop existing database link 
 drop public database link LQPVPUB;
  -- Create database link 
 CREATE DATABASE LINK LQPVPUB 6  CONNECT TO "INTEPDM" identified by "password" 8  using "LQPVPUB"

2. Create materialized view logs (remote host operations)

 CREATE MATERIALIZED VIEW LOG ON dm_basicmodel  
 WITH PRIMARY KEY  
 INCLUDING NEW VALUES;

3. Create a Materialized View

Create materialized view mv_model -- create materialized view build immediate -- create refresh fast with primary key after the VIEW is compiled -- incrementally REFRESH (FAST, incremental) based on the primary key of the master table) on demand -- when the user needs it, the user refresh enable query rewrite -- can read and write
SELECT bm_id, bm_partid, bm_code from dm_basicmodel @ LQPVPUB t -- query statement

4. Delete materialized views and logs

Logs and materialized views should be deleted separately

 DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;   

 DROP MATERIALIZED VIEW GG_ZLX_ZHU;  

5. materialized view update

CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS  BEGIN      DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');  END P_MVIEW_REFRESH;  

Note:

5.1 if you need to refresh multiple materialized views at the same time, you must use commas to connect the names of materialized views and specify the refresh method for each view.

(F. incremental refresh, c. Completely refresh ,? , Force refresh ).

5.2 After the log and materialized view are created on the current day, delete the log and create a new materialized view. Otherwise, the log cannot be refreshed incrementally.

5.3 because the materialized views written above are updated based on the primary key, the primary table must have a primary key.

6. query the refresh status

For a Complete refresh (Complete), the refresh duration is recorded in the FULLREFRESHTIM column of DBA_MVIEW_ANALYSIS.

The time that can be quickly refreshed is recorded in the INCREFRESHTIM column.

The two values are measured in seconds.

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='JOHN';