Home > TroubleShooting > exec dbms_mview.refresh returns ORA-12018: following error encountered during code generation

exec dbms_mview.refresh returns ORA-12018: following error encountered during code generation

December 21, 2009 Leave a comment Go to comments

symptoms

1. We can create the materialized view with the following statement

CREATE MATERIALIZED VIEW VI_CHANNEL_MV AS SELECT channel_key, channel_id, status, name_eng, name_chi, display_channel_name_eng, display_channel_name_chi, language FROM vi.vi_channel_t@vi_db_link;

2. We can do complete refresh the mview with complete option as the following

SQL> exec dbms_mview.refresh(‘VI_CHANNEL_MV’,’C’);

PL/SQL procedure successfully completed.

3.  But, can’t refresh the mview with dbms_view like as the following

call dbms_mview.refresh(‘VI_CHANNEL_MV’);

ERROR at line 1:

ORA-12018: following error encountered during code generation for

“EPG”.”VI_CHANNEL_MV”

ORA-00942: table or view does not exist

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2545

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2751

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2720

ORA-06512: at line 1

 Causes

1. Do session trace for refresh the mview by the command  alter session set events ‘10046 trace name context forever, level 12’;

2. Get the following detailed for the error ORA-00942

=====================
PARSE ERROR #9:len=52 dep=2 uid=99 oct=3 lid=99 tim=1533135953690 err=942 
SELECT * FROM “VI”.”MLOG$_VI_CHANNEL_T”@”VI_DB_LINK” <– this shows it can’t access the mview log table
CLOSE #9:c=0,e=0,dep=2,type=0,tim=1533135953690
WAIT #9: nam=’SQL*Net message to dblink’ ela= 0 driver id=675562835 #bytes=1 p3=0 obj#=633 tim=1569931221721
WAIT #9: nam=’SQL*Net message from dblink’ ela= 625 driver id=675562835 #bytes=1 p3=0 obj#=633 tim=1569931222368

Fixed

Logon the source database, grant select privilege to remote login user or role corresponding to that user

SQL> grant select on MLOG$_VI_CHANNEL_T  to SGDM_ROLE;

Logon the destination database, refresh the materialized view successfully

SQL> exec dbms_mview.refresh(‘VI_CHANNEL_MV’);

PL/SQL procedure successfully completed.

Advertisements
Categories: TroubleShooting
  1. Irfan
    March 27, 2012 at 2:32 pm

    Thanks for your post, it helped me to fix my issue.

  2. sushma.koyi
    August 18, 2012 at 12:52 pm

    Hi,

    It’s wonderful post.I resolved my issue just like the above.Keep posting.

    Thanks & Regards,
    Sushma K

  3. Don from ohio
    February 18, 2013 at 4:55 pm

    IT HELP MANY TIME. THANK FOR HELP AND THINGS. YOU SO gREAT

  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: