1. 创建物化视图相关的授权

GRANT CREATE MATERIALIZED VIEW TO adam;
GRANT QUERY REWRITE TO adam;

2. 使用dmbs_mview.explain_mview查询无法快速刷新的原因

需要先创建表 mv_capabilities_table
脚本：$ORACLE_HOME/rdbms/admin/ultxmv.sql
具体用法：

exec dbms_mview.explain_mview(mv=>'ekp.mv_org_person',stmt_id=>'100'); 

select * from mv_capabilities_table t
where t.capability_name like 'REFRESH_FAST_AFTER%' and
statement_id = '100'

dmbs_mview.explain_mview查询出无法快速刷新的原因：

• mv references PL/SQL function that maintains state， msnno 2083

查询语句中使用了函数wmsys.wm_concat

• inline view or subquery in FROM list not supported for this type MV

使用了标准sql的left join写法造成的，需要改成oracle的专用语法

• the SELECT list does not have the rowids of all the detail tables

select子句的字段列表必须包含from语句中所有表的rowid

• the detail table does not have a materialized view log

需要在查询表上创建materialized view log
如：create materialized view log on schema.t1 with rowid;

3. 完整示例

create materialized view log on schema.t1 with rowid;
create materialized view log on schema.t2 with rowid;
create materialized view log on schema.t3 with rowid;
create materialized view log on schema.t4 with rowid;
create materialized view log on schema.t6 with rowid;

CREATE MATERIALIZED VIEW mv_org_person
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
select t1.rowid rowid1,
t2.rowid rowid2,
t3.rowid rowid3,
t4.rowid rowid4,
t6.rowid rowid6,

t1.fd_id as fd_id,
t1.fd_name as fd_name
from t1,
t2,
t3,
t4,
t6
where t1.fd_id = t2.fd_id and
t2.fd_id = t3.fd_personid (+) and
t3.fd_postid = t4.fd_id(+) and
t1.fd_parentid = t6.fd_id(+) and
t1.fd_org_type = 8 and
t1.fd_is_available = 1

参考资料：
物化视图的快速刷新（一）
物化视图的快速刷新（二）
物化视图的快速刷新（三）