Re: ORA-12052: cannot fast refresh materialized view

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 14 Sep 2006 22:19:13 +0200
Message-ID: <eecdg0$pq0$03$1@news.t-online.com>

amy schrieb:
> 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.
>

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref537 <quote>
Restrictions on Fast Refresh on Materialized Views with Joins Only

Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

       All restrictions from "General Restrictions on Fast Refresh".
       They cannot have GROUP BY clauses or aggregates.
       Rowids of all the tables in the FROM list must appear in the 
SELECT list of the query.
       Materialized view logs must exist with rowids for all the base 

tables in the FROM list of the query.
</quote>

Or, alternatively,

ng_at_ORA102> create table emp (empid number, name varchar2(10), deptno number);

Table created.

ng_at_ORA102> alter table emp add constraint emp_pk primary key (empid);

Table altered.

ng_at_ORA102> create table dept (deptno number, name varchar2(10));

Table created.

ng_at_ORA102> alter table dept add constraint dept_pk primary key (deptno);

Table altered.

ng_at_ORA102> alter table emp add constraint emp_fk foreign key (deptno) REFERENCES dept (deptno);

Table altered.

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

Materialized view log created.

ng_at_ORA102> create materialized view log on dept with primary key including NEW values;

Materialized view log created.

ng_at_ORA102> truncate table mv_capabilities_table;

Table truncated.

ng_at_ORA102> exec dbms_mview.explain_mview('create materialized view emp_dept_mv refresh fast as select e.name from emp e, dept d where e.deptno=d.deptno');

PL/SQL procedure successfully completed.

ng_at_ORA102> select capability_name,possible,msgtxt from mv_capabilities_table t WHERE capability_name LIKE 'REFRESH_FAST%';

CAPABILITY_NAME                PO MSGTXT
------------------------------ -- 
--------------------------------------------------------------------------------
REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N  the SELECT list does not have the 
rowids of all the detail tables
REFRESH_FAST_AFTER_INSERT      N  mv log must have ROWID
REFRESH_FAST_AFTER_INSERT      N  mv log must have ROWID
REFRESH_FAST_AFTER_ONETAB_DML  N  see the reason why 
REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N  see the reason why 
REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N  PCT is not possible on any of the 

detail tables in the materialized view

7 rows selected.

Best regards

Maxim Received on Thu Sep 14 2006 - 15:19:13 CDT