说说我的问题:create materialized view mv_userinfo
refresh fast
on commit
with primary key
as
select t1.userid,t1.username,t1.sex,t1.birthday,t1.phone from userinfo@test160 t1但就是不能创建成功,提示“ora-12054无法为实体化视图设置 on commit”。
我如果把userinfo@test160换成本地的表userinfo就可以创建成功,而且在源表我也创建了物化视图日志,如下:create materialized view log on userinfo with primary key;在网上搜了很多很多,但都没能解决我的问题,有谁遇到过这种情况吗?
refresh fast
on commit
with primary key
as
select t1.userid,t1.username,t1.sex,t1.birthday,t1.phone from userinfo@test160 t1但就是不能创建成功,提示“ora-12054无法为实体化视图设置 on commit”。
我如果把userinfo@test160换成本地的表userinfo就可以创建成功,而且在源表我也创建了物化视图日志,如下:create materialized view log on userinfo with primary key;在网上搜了很多很多,但都没能解决我的问题,有谁遇到过这种情况吗?
例如:GRANT ON COMMIT REFRESH ON userinfo TO 用户;
however the following restrictions apply:
1 All tables in the materialized view must have materialized view logs, and the
materialized view logs must:
2 Contain all columns from the table referenced in the materialized view.
3 Specify with ROWID and INCLUDING NEW VALUES.
4 Specify the SEQUENCE clause if the table is expected to have a mix of
inserts/direct-loads, deletes, and updates.
5 Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast
refresh.
6 COUNT(*) must be specified.
7 For each aggregate AGG(expr), the corresponding COUNT(expr) must be
present.
8 If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and
SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be
specified. See Table 8–1 on page 8-15 for further details.
9 The SELECT list must contain all GROUP BY columns.
n10fast refresh is supported
only on conventional DML inserts and direct loads.
n Materialized views with MIN or MAX aggregates
n Materialized views which have SUM(expr) but no COUNT(expr)
n Materialized views without COUNT(*)
Such a materialized view is called an insert-only materialized view.
n The COMPATIBILITY parameter must be set to 9.0 if the materialized aggregate
view has inline views, outer joins, self joins or grouping sets and FAST
REFRESH is specified during creation. Note that all other requirements for fast
refresh specified previously must also be satisfied.
Materialized views with named views or subqueries in the FROM clause can be
fast refreshed provided the views can be completely merged. For information
on which views will merge, refer to the Oracle9i Database Performance Tuning
Guide and Reference.
n If there are no outer joins, you may have arbitrary selections and joins in the
WHERE clause.
n Materialized aggregate views with outer joins are fast refreshable after
conventional DML and direct loads, provided only the outer table has been
modified. Also, unique constraints must exist on the join columns of the inner
join table. If there are outer joins, all the joins must be connected by ANDs and
must use the equality (=) operator.
n For materialized views with CUBE, ROLLUP, Grouping Sets, or concatenation of
them, the following restrictions apply:
n The SELECT list should contain grouping distinguisher that can either be a
GROUPING_ID function on all GROUP BY expressions or GROUPING
functions one for each GROUP BY expression. For example, if the GROUP BY
clause of the materialized view is "GROUP BY CUBE(a, b) ", then the
SELECT list should contain either "GROUPING_ID(a, b) " or
"GROUPING(a) AND GROUPING(b) " for the materialized view to be fast
refreshable.
n GROUP BY should not result in any duplicate groupings. For example,
"GROUP BY a, ROLLUP(a, b) " is not fast refreshable because it results
in duplicate groupings "(a), (a, b), AND (a) ".