Received ORA-22818 subquery expressions not allowed here when creating materialized view

Hello,

I was trying to create a materialized view to describe the referential integrity among my tables:

create materialized view user_references
   tablespace tbspc
   build immediate
   using index
   refresh complete on demand next sysdate + 1
   as
   select uic.table_name to_table, uic.column_name to_column,
      ucc.table_name from_table, ucc.column_name from_column
      from user_ind_columns uic, user_constraints uc, user_cons_columns ucc
      where uic.index_name = uc.r_constraint_name
      and uc.constraint_name = ucc.constraint_name
      and uc.owner=upper('my_schema');

I was able to create this MV in Oracle  9.2. It failed with the following error when I ran it against Oracle 10.1:

                from user_ind_columns uic, user_constraints uc, user_cons_columns ucc
                     *
ERROR at line 9:
ORA-22818: subquery expressions not allowed here

Is not allowing subqueries in MV a new restriction in Oracle 10? Is there a workaround?

Thanks
bertchan2003Asked:
Who is Participating?
 
TaygerConnect With a Mentor Commented:
Hello

Your statement looks fine to me.

Try first to get/make sure the following grants before creating the MV:
grant query rewrite to <MV_creation_user>;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

There is a good link about init.ora parameters for creating MVs:
http://www.akadia.com/services/ora_materialized_views.html

Hope this helps.
Tayger
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.