Re: ORA-12052: cannot fast refresh materialized view


amy wrote:
I have problem performing a fast refresh on a materialized view. I went
through the documentation and it looks like I have met all the
criterias for a fast refresh but I can't seems to get the MV created.

Can someone point out what I have done incorrectly?

create table emp (empid number, name varchar2(10), deptno number);
alter table emp add constraint emp_pk primary key (empid);
create table dept (deptno number, name varchar2(10));
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_fk foreign key (deptno) references
dept (deptno);

create materialized view log on emp with primary key (name,deptno)
including new values;
create materialized view log on dept with primary key including new
values;

SQL> create materialized view emp_dept_mv
2 refresh fast as
3 select e.name from emp e, dept d where e.deptno=d.deptno;
select e.name from emp e, dept d where e.deptno=d.deptno
*
ERROR at line 3:
ORA-12052: cannot fast refresh materialized view GTTK.EMP_DEPT_MV

thanks in advance.

For starters:


CAPABILITY_NAME P RELATED_NUM MSGNO MSGTXT
------------------------------ - ----------- ----------
----------------------------------------------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N 45 2068 relation is not
a partitioned table
PCT_TABLE N 52 2068 relation is not
a partitioned table
REFRESH_FAST_AFTER_INSERT N 2172 the SELECT list
does not have the rowids of all the detail tables
REFRESH_FAST_AFTER_INSERT N 2079 mv log must
have ROWID
REFRESH_FAST_AFTER_INSERT N 2079 mv log must
have ROWID
REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason
why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason
why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N 2157 PCT is not
possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N 2158 general rewrite
is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE N 45 2068 relation is not
a partitioned table
PCT_TABLE_REWRITE N 52 2068 relation is not
a partitioned table

18 rows selected.

Making this change eliminates the missing rowid problems:

create materialized view log on emp with rowid, sequence(empid, name,
deptno) including new values;
create materialized view log on dept with rowid, sequence(deptno, name)
including new values;

I haven't time to play with this to work out the required query; the
key, apparently, is having the select list return all rowids from the
dept table (my educated guess). Possibly someone else can show where
I've gone wrong and provide assistance.


David Fitzjarrell

.

Relevant Pages

  • Re: ORA-12052: cannot fast refresh materialized view
    ... create table emp, deptno number); ... alter table dept add constraint dept_pk primary key; ... have ROWID ...
    (comp.databases.oracle.server)
  • Re: ORA-12052: cannot fast refresh materialized view
    ... create table emp, deptno number); ... alter table dept add constraint dept_pk primary key; ... Restrictions on Fast Refresh on Materialized Views with Joins Only ...
    (comp.databases.oracle.server)
  • Re: Query 8 Tables no Primary Key
    ... Another WAG is that Emp# ... should be a primary key in an employee table and foreign key in other ... an idea how I can fix my problem I would be grateful. ...
    (microsoft.public.access.queries)
  • Re: What is the rowid composed of?
    ... >> But quoting only part of the book is misleading - ... >Glossary: ROWID ... >Oracle creates logical rowids based on the primary key of the table. ... >logical rowids do not change as long as the primary key does not change. ...
    (comp.databases.oracle.misc)
  • ORA-12052: cannot fast refresh materialized view
    ... I have problem performing a fast refresh on a materialized view. ... create table emp, deptno number); ... alter table dept add constraint dept_pk primary key; ...
    (comp.databases.oracle.server)