Skip to Main Content
  • Questions

  • not able to re-create materialized view on prebuilt table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, prashant.

Asked: July 13, 2016 - 7:30 am UTC

Last updated: July 14, 2016 - 9:22 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Steps I am trying to execute :
CREATE TABLE sample.MV(application_mode varchar2(25));

CREATE MATERIALIZED VIEW sample.MV
ON PREBUILT TABLE
REFRESH FORCE ON DEMAND
AS
SELECT application_mode
  FROM  sample.tbl_name
  WHERE cnt > 0
  GROUP BY modes;

when any other user is trying to drop and re-create the MV
DROP MATERIALIZED VIEW sample.MV;
CREATE MATERIALIZED VIEW sample.MV
ON PREBUILT TABLE
REFRESH FORCE ON DEMAND
AS
SELECT application_mode
  FROM  sample.tbl_name
  WHERE cnt > 0
  GROUP BY modes;

he gets error while re-creation: ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV

why is so?

User got a work around for the same which is to drop the table first and then recreate the table , recreate the MV.

Really weird behaviour of oracle is that all the subsequent attempts of that user of dropping and re-creating MV work well with no error.

and Chris said...

From the docs:

You cannot create both a materialized view and a prebuilt materialized view on the same table. For example, If you have a table costs with a materialized view cost_mv based on it, you cannot then create a prebuilt materialized view on table costs. The result would make cost_mv a nested materialized view and this method of conversion is not supported.

https://docs.oracle.com/database/121/DWHSG/basicmv.htm#DWHSG0083

So you get this error when:

- You have a materialized view that uses a table, T
- You're building a new MV that uses T as a prebuilt table:

create table t (
  application_mode number
);
create table mv(application_mode number);

insert into t
  select rownum from dual connect by level <= 10;
insert into mv
  select rownum from dual connect by level <= 10;
commit;

create materialized view t_mv
refresh force on demand
as
select  application_mode
  from  mv;
  
create materialized view mv
on prebuilt table
refresh force on demand
as
select  application_mode
  from  t;

SQL Error: ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV


So at some point you had another materialized view queried the table MV.

Dropping and re-creating the table invalidates the original MV. Then you can create the MV using the table as you've seen. Continuing from above:

drop table mv;
create table mv(application_mode number);
create materialized view mv
on prebuilt table
refresh force on demand
as
select  application_mode
  from  t;

select mview_name, staleness from user_mviews;

MVIEW_NAME  STALENESS      
T_MV        NEEDS_COMPILE  
MV          UNKNOWN   

select object_type, status from user_objects
where  object_name = 'T_MV';

OBJECT_TYPE        STATUS   
TABLE              VALID    
MATERIALIZED VIEW  INVALID


See also:

http://rwijk.blogspot.co.uk/2009/08/fast-refreshable-materialized-view.html

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

review is knowledgeable but not the solution to my issue

prashant mishra, July 14, 2016 - 8:54 am UTC

Needed to change my create table ddl like below : 
create table mv as 
SELECT application_mode FROM tbl_name 
WHERE 1=2;


I am able to drop and recreate the materialized view without any error.

Do you have some reason why a table ddl like above has resolved the issue but 
create table mv(application_mode varchar2(25));



Chris Saxon
July 14, 2016 - 9:22 am UTC

I'm not able to reproduce your error any other way!

In session 1:
SQL> create table tbl_name (
  2    application_mode varchar2(25),
  3     cnt int
  4  );

Table created.

SQL> CREATE TABLE MV(application_mode varchar2(25));

Table created.

SQL>
SQL> CREATE MATERIALIZED VIEW MV
  2  ON PREBUILT TABLE
  3  REFRESH FORCE ON DEMAND
  4  AS
  5  SELECT application_mode
  6    FROM  tbl_name
  7    WHERE cnt > 0
  8    GROUP BY application_mode;

Materialized view created.

And in session 2:
SQL> DROP MATERIALIZED VIEW MV;

Materialized view dropped.

SQL> CREATE MATERIALIZED VIEW MV
  2  ON PREBUILT TABLE
  3  REFRESH FORCE ON DEMAND
  4  AS
  5  SELECT application_mode
  6    FROM  tbl_name
  7    WHERE cnt > 0
  8    GROUP BY application_mode;

Materialized view created.

Can you provide a complete test case?

As a side note, the query in your MV looks fishy to me. I get an error running it!

SQL> drop table tbl_name purge;

Table dropped.

SQL> create table tbl_name (
  2    application_mode varchar2(25),
  3     cnt int,
  4     modes int
  5  );

Table created.

SQL>
SQL> SELECT application_mode
  2    FROM  tbl_name
  3    WHERE cnt > 0
  4    GROUP BY modes;
SELECT application_mode
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

review is knowledgeable but not the solution to my issue

prashant mishra, July 14, 2016 - 8:55 am UTC

there is no other mv pointing to the table.

I have got some work around for issue though I am not able to get the logic behind it.

Needed to change my create table ddl like below to fix the issue: 
create table mv as 
SELECT application_mode FROM tbl_name 
WHERE 1=2;


I am able to drop and recreate the materialized view without any error.

Do you have some reason why a table ddl like above has resolved the issue but 
create table mv(application_mode varchar2(25));



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.