oracle materialized view 不刷新,Oracle Materialized view

Third party system has one table, we want to fetch new changed

data into one table.

It means that the new table should contain all added data or deleted data or updated data.

1.Source system

Create table xx_test

Create materialized log

Example:

CREATE MATERIALIZED VIEW LOG ON

ORG_BASEINFO

with rowid including new values

drop materialized view log on xx_test

2.Target system

Create database linktarget_link_to_source

example:

create database link LINK_I1

connect to apps identified by apps

using '   (DESCRIPTION =

(ADDRESS_LIST =

(FAILOVER = yes)

(LOAD_BALANCE = yes)

(ADDRESS = (PROTOCOL = TCP)(HOST = usmtnnpdinfdr20.dev.emrsn.org)(PORT = 35501))

(ADDRESS = (PROTOCOL = TCP)(HOST = usmtnnpdinfdr21.dev.emrsn.org)(PORT = 35501))

)

(CONNECT_DATA =

(SERVICE_NAME = onidai1_OLTP)

)

)

';

Drop materializedviewxx_test_mv

create

materialized view xx_test_mv refresh fast on DEMAND with rowid as

select id from

xx_test@ target_link_to_source

if you want to sync and refresh materialized view data,

please use

BEGIN dbms_mview.refresh('MV_ORG_MOREINFO','F');END;

You also can create a job in Oracle

declare

job_id number;

begin

DBMS_JOB.submit(job =>job_id,what => 'begin

dbms_mview.refresh(''MV_ORG_BASEINFO'',''F'');dbms_mview.refresh(''MV_ORG_MOREINFO'',''F'');end;',next_date

=> sysdate,interval => 'sysdate + 10.0/(60*60*24)');

COMMIT;

end;

common question and checking points:

1.This is log table in the source database MLOG$_table_name

2.dba_mview_logs--checking all view log

3.user_mviews–checing all materialized view

4.please ensure you should have select privilege for source table and source log

table.

issue:

it is not working, it shows below error

CREATE MATERIALIZED VIEW APPS.XX_TEST_MV

REFRESH FAST ON DEMAND

WITH ROWID

AS

SELECT ID, NAME FROM XX_TEST@LINK_I1;

it is not working, it shows below error

CREATE MATERIALIZED VIEW APPS.XX_TEST_MV_TEST

REFRESH FORCE ON DEMAND

WITH ROWID

AS

SELECT ID, NAME FROM ERP.XX_TEST@PLM.WORLD

-- no idea, why only this one is working

CREATE MATERIALIZED VIEW XX_TEST_MV_TEST

REFRESH FORCE ON DEMAND

AS SELECT ID, NAME FROM ERP.XX_TEST@PLM.WORLD;