eg:
CREATE MATERIALIZED VIEW MV_ASSET_QUERY_COUNT
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT COUNT(T.SBBH)
FROM PMSBI.F_SB_SBXX T, D_PM_COMPANY T1, PMSBI.D_BDS T2, D_PM_ASSET_STYLE T4,D_PM_ZZC T5,D_PM_ASSET_TYPE T6
WHERE T.SSJDM = T1.DWDM AND T.BDSDM = T2.BDSDM AND T.SBZLDM = T4.SBLXDM AND T.ZZCDM = T5.ZZCDM AND T.XHDM = T6.XHDM;
如上创建物化视图,
大家都知道目标用户需要给创建用户 CREATE MATERIALIZED VIEW 权限
但是只是这样还是会报权限错误
如何处理：
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
因为在创建该物化视图的时候带了两个参数 需要把使用这两个权限给创建用户
GRANT ON COMMIT REFRESH to ANALYSIS;
GRANT GLOBAL QUERY REWRITE TO analysis;
这样就可以了...
其他同理