ORA: 12054 Creating Materialized View with UNION ALL

Question

I seem to get ORA: 12054 when I do this action. I would really appreciate some help. Thank you

create table daniel (id number, name varchar2(40));
alter table daniel add constraint daniel_pk primary key(id);
create materialized view log on daniel with rowid;

create materialized view mv_test
refresh fast on commit as
select id, name, rowid rid
from daniel
UNION ALL
select id, name, rowid rid
from daniel;

The creation of the materialized with throws the ORA.

Thanks!

Welcome to SO. If you do a search, there is a ton of answers, for example - http://stackoverflow.com/questions/8883662/why-does-a-ora-12054-error-occur-when-creating-this-simple-materialized-view-exa — OldProgrammer, Jul 28 '13 at 19:24
I suspect that this SQL is considered "too complex" to be eligible for fast refresh, perhaps because there's no unique key in the view..? — Bob Jarvis - Слава Україні, Jul 28 '13 at 19:38

score 1 · Answer 1 · answered Jul 29 '13 at 07:22

Oracle hurls the ORA-12054 exception because of that UNION ALL. It doesn't like the duplicates. Remove the ALL keyword and you have a valid MView, but also a tiresome one: you might as well remove the second query altogether, because you won't have duplicates anyway.

Of course, the bogus nature of the MView query may just be an artefact of the way you've simplified a test case to post on SO. If so, it would help us to understand your situation if you presented a more realistic scenario.

Note that although removing the ALL gives you a valid MView you still won't be able to create it. Your MView Log specifies only ROWID, which means you can't create an MView with the REFRESH FAST option. Either you go for REFRESH FULL or you need to include the primary key in the Log specification:

SQL> create materialized view mv_test
refresh fast on commit as
select id, name, rowid rid
from daniel  2    3    4  
  5  /
from daniel
     *
ERROR at line 4:
ORA-23415: materialized view log for "APC"."DANIEL" does not record the primary key


SQL> drop materialized view log on daniel ;

Materialized view dropped.

SQL> create materialized view log on daniel with rowid, primary key;

Materialized view log created.

SQL> create materialized view mv_test
refresh fast on commit as
select id, name, rowid rid
from daniel  2    3    4  
  5  /

Materialized view created.

SQL>

ORA: 12054 Creating Materialized View with UNION ALL

1 Answers1