//主数据库
create table t_test (a char(6));
alter table t_test add constraint pk_test primary key (a);Create materialized view log on t_test;
//备份数据库
CREATE MATERIALIZED VIEW mv_a
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/1440
WITH PRIMARY KEY
AS SELECT * FROM t_test@dblink;select * from mlog$_t_test@dblink;insert into t_test@dblink('7');
commit;select * from t_test@dblink;
1
2
3
select * from t_test;
Nullbegin
dbms_refresh.refresh('mv_a');
end;
begin
dbms_mview.refresh('mv_a');
end;无论怎么操作都不能刷新,数据库是oracle9i
create table t_test (a char(6));
alter table t_test add constraint pk_test primary key (a);Create materialized view log on t_test;
//备份数据库
CREATE MATERIALIZED VIEW mv_a
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/1440
WITH PRIMARY KEY
AS SELECT * FROM t_test@dblink;select * from mlog$_t_test@dblink;insert into t_test@dblink('7');
commit;select * from t_test@dblink;
1
2
3
select * from t_test;
Nullbegin
dbms_refresh.refresh('mv_a');
end;
begin
dbms_mview.refresh('mv_a');
end;无论怎么操作都不能刷新,数据库是oracle9i
完全刷新
BEGIN dbms_mview.refresh(list => 'mv_a' method => 'c'); END; *
第 1 行出现错误:
ORA-06550: 第 1 行, 第 41 列:
PLS-00103: 出现符号 "METHOD"在需要下列之一时:
. ( ) , * @ % & = - + <
/ > at in is mod not rem <an exponent (**)> <> or != or ~= >=
<= <> and or like between ||
符号 "(" 被替换为 "METHOD" 后继续。
ORA-06550: 第 1 行, 第 55 列:
PLS-00103: 出现符号 ";"在需要下列之一时:
. ( ) , * % & = - + < / > at
in is mod not rem <an exponent (**)> <> or != or ~= >= <= <>
and or like between ||
符号 ")" 被替换为 ";" 后继续。
SQL>
exec dbms_mview.refresh(list => 'mv_a' , method => 'c');
SELECT * FROM t_test@dblink;
你试试能不能访问到数据
跟备份数据库的物化视图mv_a没有设置关联.
正确是
1.先在备份数据库建t_test(空表).
2.建物化视图
CREATE MATERIALIZED VIEW t_test
on prebuilt table
REFRESH FORCE WITH PRIMARY KEY
START WITH SYSDATE
NEXT SYSDATE + 1/1440
AS SELECT * FROM t_test@dblink;
3.初始化数据同步.
exec dbms_refresh.refresh('t_test','Complete')
4.而后每隔1分钟就自动同步一次数据(建MV时设置的).