Oracle DBA ve Apps DBA blog

appsdba,e-business suite,dba

ORA-12052: cannot fast refresh materialized view

leave a comment »

materialized view yaratılmak istenildiğinde alınan bu hata join içeren sql ler den kaynaklanabilmektedir.

—————————————————–
create table tb
(c1 varchar2(10) not null,
c2 varchar2(10) not null,
c3 number(2),
constraint primary key(c1));
—————————————————–
CREATE MATERIALIZED VIEW KOPDEV.MV_T1
BUILD IMMEDIATE
refresh fast
as select t1.c1,t1.c2,t1.c3,
t2.c1,t2.c2,t2.c3
from tb t1,tb t2
where t1.c1=t2.c1;
—————————————————–
bu kullanım ORA-12052 hatasına neden olmaktadır.Çözüm olarak select ifadesi içerisine kullanılan tabloların rowid değerleride alınır.Aynı kolon ismi olmaması için alias kullanılır.
—————————————————–
CREATE MATERIALIZED VIEW KOPDEV.MV_T1
BUILD IMMEDIATE
refresh fast
as select t1.rowid t1_row,t2.rowid t2_row,
t1.c1,t1.c2,t1.c3,
t2.c1,t2.c2,t2.c3
from tb t1,tb t2
where t1.c1=t2.c1;

Written by Erdem Çer

12/08/2010 at 6:22 am

Posted in dba, tip

Tagged with ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: