标题: ORA-00942 ORA-2063 When Running A Query On View Using a DBLink [打印本页] 作者: 刘泽宇 时间: 2024-1-28 12:35 标题: ORA-00942 ORA-2063 When Running A Query On View Using a DBLink 现象：
When attempting to query a view which selects from a remote table via a database link the following errors occur:
ORA-00942: table or view does not exist
ORA-02063: preceding line from DB_LINK_1
测试案例:
1. connect USER1
2. CREATE DATABASE LINK "DB_LINK_1" CONNECT TO USER1 IDENTIFIED BY <Password> USING 'DB_LINK_1';
3. CREATE OR REPLACE FORCE VIEW USER2.V1 as select * FROM T1@DB_LINK_1;
4. grant select on USER2.V1 to USER1;
5. connect USER1
6. select count(*) from USER2.V1 where rownum<4
ORA-00942: table or view does not exist
ORA-02063: preceding line from DB_LINK_1
原因：
The DDL of the view does not specify the owner of the remote table :
CREATE OR REPLACE FORCE VIEW USER2.V1 as select * FROM T1@DB_LINK_1;
Since there is no owner specified explicitly it uses the database link user.
解决方法：
Specify the owner of the remote table:
sqlplus / as sysdba
CREATE OR REPLACE FORCE VIEW USER2.V1 as select * FROM USER1.T1@DB_LINK_1;