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
REFRESH FORCE ON DEMAND
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