create view select from synonym

oracle11g升级19c 导入view时出现问题

CREATE OR REPLACE VIEW USR1.TEST AS SELECT 
EMP_ID from synonym_name;

11g执行成功

19c执行失败

原因：

The FROM clause of the defining subquery of the view can reference only a single existing database table. Joins are not permitted. The base table must be in the same schema as the view being created. You cannot use a synonym to identify the table, but you can specify a table alias.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30

修改案：

1.利用SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME IN ( 'synonym_name');查找到对应的实体表。

CREATE OR REPLACE VIEW USR1.TEST AS SELECT 
EMP_ID from USR2.table synonym_name;

*******************此时出现了新的问题：提示表不存在

原因：The base table must be in the same schema as the view being created.

解决案：grant select on USR2.table to USR1;