Re: Can NOT create Materialized View

Maybe will help, if you create on base tables "MATERIALIZED VIEW LOG"

regards, Matej

"M. Mehta" <mlal72_at_yahoo.com> wrote in message news:a7fd3ab8.0405251203.5e429a57_at_posting.google.com...
> I gave the following permission: grant create materialized view to hr;
> and I still get the following error:ORA-12054: cannot set the ON
> COMMIT refresh attribute for the materialized view.
>
> Here is what I am doing: ( Release 9.2.0.4.0 )
>
> CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE
> ON COMMIT AS
> SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal)
> FROM EMP a, DEPT b
> WHERE a.dept_id = b.dept_id (+)
> GROUP BY b.dept_id, b.dept_name
>
> Frank van Bortel <fvanbortel_at_netscape.net> wrote in message
news:<c8tg9u$d6n$1_at_news3.tilbu1.nb.home.nl>...
> > M. Mehta wrote:
> > > It seems that you can not create a materialized view if you are using
> > > outer joins...can someone please verify this?
> > >
> > > Thanks
> > > M. Mehta
> > >
> > > Please follow my example below:
> > >
> > > created 2 tables:
> > >
> > > select * from emp;
> > >
> > > EMP_ID DEPT_ID SAL
> > > ---------- ---------- ----------
> > > 1 1 20000
> > > 2 1 50000
> > >
> > > select * from dept;
> > >
> > > DEPT_ID DEPT_NAME
> > > ---------- ----------
> > > 1 HR
> > > 2 IT
> > > 3 CC
> > >
> > > create materialized view (with out outer join):
> > > SQL> create materialized view mv_test build immediate refresh complete
> > > on commit as
> > > 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
> > > count(sal)
> > > 3 from emp a, dept b
> > > 4 where a.dept_id = b.dept_id
> > > 5 group by b.dept_id, b.dept_name
> > > 6 /
> > >
> > > Materialized view created.
> > >
> > > SQL> drop materialized view mv_test;
> > >
> > > create materialized view (WITH outer join):
> > > SQL> create materialized view mv_test build immediate refresh complete
> > > on commit as
> > > 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
> > > count(sal)
> > > 3 from emp a, dept b
> > > 4 where a.dept_id = b.dept_id (+)
> > > 5 group by b.dept_id, b.dept_name
> > > 6 /
> > > from emp a, dept b
> > > *
> > > ERROR at line 3:
> > > ORA-12054: cannot set the ON COMMIT refresh attribute for the
> > > materialized view
> >
> > Versions may help... this is 9.2.0.4:
> > SQL> connect system/manager
> > Connected.
> > SQL> grant create materialized view to hr;
> >
> > Grant succeeded.
> >
> > SQL> connect hr/hr
> > Connected.
> > SQL> _at_t
> >
> > Materialized view created.
> >
> > SQL> l
> > 1 create materialized view mv_test build immediate refresh complete
> > 2 on commit as
> > 3 select count(*), b.department_id, b.department_name, sum(salary)
> > salary,cou
> > nt(salary)
> > 4 from employees a, departments b
> > 5 where a.department_id = b.department_id (+)
> > 6* group by b.department_id, b.department_name
Received on Wed May 26 2004 - 21:39:45 CEST