Oracle 11g创建远程物化视图时提示“ORA-01788: 此查询块中要求 CONNECT BY 子句”的解决方案

时间:2022-11-19 06:29:09

将远程服务器的一个表映射为本地的一个物化视图,使用下列语句:

----建立测试环境DBLINK:
create database link lnk
connect
to user1
identified
by pwd1
using
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 172.17.211.114)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID= REMOTEDB)))';

--创建物化视图,每隔两天自动同步更新一次;
CREATE MATERIALIZED VIEW DEPARTMENT
REFRESH FORCE
START
WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM user1.DEPARTMENT@lnk WHERE 11;

 

结果提示:"ORA-01788: 此查询块中要求 CONNECT BY 子句"。

Oracle 11g创建远程物化视图时提示“ORA-01788: 此查询块中要求 CONNECT BY 子句”的解决方案

google无法访问,百度搜索太扯蛋,用必应,总算得到线索,解决方案:

在本地库中执行如下语句:

ALTER SYSTEM SET "_ALLOW_LEVEL_WITHOUT_CONNECT_BY"=TRUE SCOPE=BOTH 

 

OK!