跨Schema快速刷新Oracle物化视图遇ORA-12018错误求助

问题背景 #

在Oracle 19c Enterprise Edition Release 19.17.0.0.0版本中，跨Schema创建的物化视图执行快速刷新（FAST refresh）时失败，抛出ORA-12018错误，底层原因是权限不足。

操作场景 #

SCHEMA_A（源表所属Schema）的操作 #

创建源表、物化视图日志并授予SCHEMA_B基础查询权限：

create table test_new_20230508 (pname varchar(100) PRIMARY KEY );

insert into TEST_NEW_20230508 (PNAME) values ('some name');

CREATE MATERIALIZED VIEW LOG ON TEST_NEW_20230508 WITH ROWID, PRIMARY KEY;

grant select on TEST_NEW_20230508 to SCHEMA_B;

commit;

SCHEMA_B（物化视图所属Schema）的操作 #

创建物化视图和查询操作正常，但执行快速刷新时失败：

-- 执行成功
CREATE MATERIALIZED VIEW TEST_NEW_20230508_MV AS select * from SCHEMA_A.TEST_NEW_20230508;
-- 执行成功
SELECT * FROM TEST_NEW_20230508_MV;

-- 执行失败
BEGIN
    DBMS_MVIEW.REFRESH('TEST_NEW_20230508_MV', 'F');
END;

报错详情 #

[2023-05-08 16:09:25] [72000][12018]
[2023-05-08 16:09:25]   ORA-12018: following error encountered during code generation for "SCHEMA_B"."TEST_NEW_20230508_MV"
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
[2023-05-08 16:09:25]   ORA-00942: table or view does not exist
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
[2023-05-08 16:09:25]   ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
[2023-05-08 16:09:25]   ORA-06512: at line 2
[2023-05-08 16:09:25] Position: 0

关键现象 #

• 仅创建含ROWID的物化视图日志，且不使用FAST刷新时，操作全部正常；
• 物化视图创建在源Schema（SCHEMA_A）内时，FAST刷新可正常执行。

权限补充方案 #

报错核心是SCHEMA_B缺少访问源表物化视图日志及主键索引的权限，需在SCHEMA_A下执行以下授权：

1. 授予SCHEMA_B访问物化视图日志的权限：

GRANT SELECT ON MLOG$_TEST_NEW_20230508 TO SCHEMA_B;

2. 授予SCHEMA_B访问源表主键索引的权限：

GRANT SELECT ON TEST_NEW_20230508_PK TO SCHEMA_B;

注：TEST_NEW_20230508_PK是源表主键对应的系统默认索引名，实际环境中若索引为自定义名称，需替换为真实索引名称。

完成上述授权后，重新执行SCHEMA_B中的快速刷新操作即可成功。

内容的提问来源于stack exchange，提问作者user103716