Sunday, 25 May 2014

Refresh of Mview is Throwing ORA-00942


Refresh of Mview is throwing ORA-942 after successful creation.

Create materialized view schema_a.client_mview 
tablespace users build immediate
refresh force start with trunc(sysdate)+1455/1440
next trunc(sysdate)+1455/1440
as
select client_id, username, createdate, swver
from client_req@TEST_LINK
where swver is not null;

Materialized View Created.

select db_link, username from dba_db_links where db_link like 'TEST_LINK';

DB_LINK                      USERNAME
--------------------      ------------------------------
TEST_LINK.COM            SCHEMA_B

On the TEST_LINK.COM database, the db link user SCHEMA_B has select granted directly on the master tables.
select grantee,owner,table_name,privilege from user_tab_privs
where table_name like 'CLIENT%'

GRANTEE        OWNER      TABLE_NAME      PRIVILEGE
-------------     ------------     ------------------    ------------------
SCHEMA_B     SCHEMA_A    CLIENT_REQ     SELECT

Trying to refresh the materialized views returns errors:

SQL> execute dbms_refresh.refresh('"SCHEMA_A"."CLIENT_MVIEW"');
BEGIN dbms_refresh.refresh('"SCHEMA_A"."CLIENT_MVIEW"'); END;

*
ERROR at line 1:
ORA-12018: following error encountered during code generation for "SCHEMA_A"."CLIENT_MVIEW"
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Cause
Database link user (SCHEMA_B) doesn't have select privileges on the MLOG$_CLIENT_REQ.
Log table is in the master table owner's schema (SCHEMA_A).
The problem was identified using the trace file.

Solution
To implement the solution, please execute the following steps:
grant select on MLOG$_<Table Name> to <Database link user>;
or
grant select any table to <Database link user>;

Example:
grant select on MLOG$_CLIENT_REQ to SCHEMA_B;
or
grant select any table to SCHEMA_B;



0 comments:

Post a Comment

Total Pageviews