Skip to Main Content

Materialized view on prebulit table

1909Jul 15 2014 — edited Jul 15 2014

Hi All,

I have created materialized view on prebuilt table, my approach is as follows.

Base table:

create table test1

(idn number,

iname varchar2(10));

insert into test1 values(1,'aa');

insert into test1 values(2,'cc');

Creating prebuilt table:

create table test2 as

select * from test1 where 1=2;

Now creating materialized view with same name of prebuilt table.

create materialized view test2 ON PREBUILT TABLE


AS select * from test1;

Now adding one extra column to prebuilt table

alter table test2 add flag varchar2(1);

Now first time i am doing complete refresh of mview

exec dbms_mview.refresh('test2','C');

Now problem here is...

1. If the BAse table test1 has the primary key on  it then mview test2 is getting refreshed with out any issue.

2. If the Base table dosn't has the primary key on it then mview refresh giving the following error.

Error starting at line : 59 in command -

exec dbms_mview.refresh('test2','C')

Error report -

ORA-12018: following error encountered during code generation for "BIUSER"."TEST2"

ORA-00947: not enough values

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431

ORA-06512: at line 1

12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""

*Cause:    The refresh operations for the indicated materialized view could

           not be regenerated due to errors.

*Action:   Correct the problem indicated in the following error messages and

           repeat the operation.

my requirement is to create mview on prebuilt table where i dont have primary key on base table and i have to add some extra columns to mview once it is created, please help me to resolve this issue

This post has been answered by Moazzam on Jul 15 2014
Jump to Answer
Post Details
Added on Jul 15 2014