--- title: 同义词嵌套场景下会导致视图访问异常-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 同义词嵌套场景下会导致视图访问异常相关的常见问题和使用技巧，帮助您快速解决 同义词嵌套场景下会导致视图访问异常的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 同义词嵌套场景下会导致视图访问异常 更新时间：2024-01-22 01:56 适用版本： V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 在 OceanBase 数据库中，存在同义词嵌套的场景下，视图访问会出现异常。 示例：使用其它用户创建的视图，且视图中包含了同义词，查询报错。 1. 创建测试用户。 ```shell obclient [SYS]> create user test01 identified by "****"; test02 identified by "123456"; ``` ```shell obclient [SYS]> create user test03 identified by "****"; Query OK, 0 rows affected (0.033 sec) ``` ```shell obclient [SYS]> create user test02 identified by "****"; Query OK, 0 rows affected (0.012 sec) ``` ```shell obclient [SYS]> create user test03 identified by "****"; Query OK, 0 rows affected (0.015 sec) ``` 2. 赋权限。 ```shell obclient [SYS]> grant connect,resource to test01; connect,resource to test02; ``` ```shell obclient [SYS]> grant connect,resource to test03; Query OK, 0 rows affected (0.044 sec) ``` ```shell obclient [SYS]> grant connect,resource to test02; Query OK, 0 rows affected (0.020 sec) ``` ```shell obclient [SYS]> grant connect,resource to test03; Query OK, 0 rows affected (0.016 sec) ``` 3. 用户 test01 创建表与视图 v1 并赋权。 ```shell obclient [SYS]> connect test01; Connection id: 3221697034 Current database: TEST01 ``` ```shell obclient [TEST01]> create table t1(a number); Query OK, 0 rows affected (0.029 sec) ``` ```shell obclient [TEST01]> create view v1 as select * from t1; Query OK, 0 rows affected (0.029 sec) ``` ```shell obclient [TEST01]> grant select on v1 to test02 with grant option; Query OK, 0 rows affected (0.029 sec) ``` 4. 用户 test02 基于用户 test01 的视图 v1 创建同义词创建一个外层视图 mv1 并赋权。 ```shell obclient [TEST01]> connect test02; Connection id: 3221697476 Current database: TEST02 ``` ```shell obclient [TEST02]> create synonym v1 for test01.v1; Query OK, 0 rows affected (0.018 sec) ``` ```shell obclient [TEST02]> create view mv1 as select * from v1; Query OK, 0 rows affected (0.012 sec) ``` ```shell obclient [TEST02]> grant select on test02.mv1 to test03; Query OK, 0 rows affected (0.010 sec) ``` 5. 用户3创建用户 test02 的基于外层视图 mv1 的同义词。 ```shell obclient [TEST02]> connect test03; Connection id: 3221697642 Current database: TEST03 ``` ```shell obclient [TEST03]> create synonym mv1 for test02.mv1; Query OK, 0 rows affected (0.014 sec) ``` 6. 用户 test03 无法访问用户 test02 的视图 mv1。 ```shell obclient [TEST03]> select * from mv1; ERROR 4063 (42S22): ORA-04063: view 'TEST02.MV1' has errors ``` ```shell obclient [TEST03]> select * from TEST02.MV1; ERROR 4063 (42S22): ORA-04063: view 'TEST02.MV1' has errors ``` 7. 需要再给 test02.v1 建个同义词才可以访问。 ```shell obclient [TEST02]> connect test03; Connection id: 3221704679 Current database: TEST03 ``` ```shell obclient [TEST03]> create synonym v1 for test02.v1; Query OK, 0 rows affected (0.018 sec) ``` ```shell obclient [TEST03]> select * from mv1; Empty set (1.515 sec) ``` ```shell obclient [TEST03]> select * from TEST02.MV1; Empty set (1.006 sec) ``` ## 适用版本 OceanBase 数据库 v3.x 版本。 ## 问题原因 该问题是由于同义词反拼时的一个 bug 引起，反拼时没有带上 database_name。 ## 解决方法 升级至问题已修复版本。 目前已修复的版本包括 OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 之后版本、V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 之后版本。 上一篇 [普通租户下 SELECT DBA_OB_LS_LOCATIONS 超时报错 -5150](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000233832) 下一篇 [OceanBase 数据库 Oracle 模式查询 user_tab_columns 报错 -5222, Incorrect value](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209988) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线