Wikipedia

Search results

ORA-00942 When Commit DML Transaction on a Base Table Belonging to a MV

A materialized view is based on 2 tables owned by 2 schemas and is refreshed FAST on COMMIT. When the user owning one of the 2 base tables, but not the materialized view, runs a DML statement on the table, it gets the ORA-942 error at COMMIT:

Lets demo :- 

SQL> conn system/manager
Connected.

SQL> drop user user1 cascade;
User dropped.

SQL> drop user user2 cascade;
User dropped.

SQL> create user user1 identified by user1;
User created.

SQL> create user user2 identified by user2;
User created.

SQL> grant CREATE SESSION, CREATE TABLE , UNLIMITED TABLESPACE,
2 CREATE MATERIALIZED VIEW, GLOBAL QUERY REWRITE
3 to user1, user2;

Grant succeeded.

SQL> conn user1/user1
Connected.

SQL> create table table1 (t1_id number, sometext varchar2(10));
Table created.

SQL> ALTER TABLE TABLE1 
2 ADD (CONSTRAINT TABLE1_PK PRIMARY KEY(t1_ID));

Table altered.

SQL> CREATE MATERIALIZED VIEW LOG ON table1
2 WITH SEQUENCE, ROWID 
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> grant all on table1 to user2;
Grant succeeded.

SQL> grant references on table1 to user2;
Grant succeeded.

SQL> insert into table1 values (1, 'Hello');
1 row created.

SQL> commit;
Commit complete.


SQL> conn user2/user2
Connected.

SQL> create table table2 (t2_id number, t1_t1_id number, sometext varchar2(10));
Table created.

SQL> ALTER TABLE TABLE2
2 ADD (CONSTRAINT TABLE2_PK PRIMARY KEY(t2_ID));
Table altered.

SQL> CREATE MATERIALIZED VIEW LOG ON table2
2 WITH SEQUENCE, ROWID
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> grant all on table2 to user1;
Grant succeeded.

SQL> ALTER TABLE TABLE2
2 ADD (CONSTRAINT TABLE2_FK FOREIGN KEY(T1_T1_ID)
3 REFERENCES user1.TABLE1(T1_ID)) ;
Table altered.

SQL> ALTER TABLE TABLE2
2 MODIFY 
3 CONSTRAINT TABLE2_FK RELY;
Table altered.

SQL> insert into table2 values (1, 1, 'Goodbye');
1 row created.

SQL> conn user1/user1
Connected.

SQL> create materialized view mv_test 
2 as SELECT u1.rowid "U1_RID", u2.rowid "U2_RID",
3 u1.sometext text1, u2.sometext text2
4 FROM user1.table1 u1, user2.table2 u2
5 WHERE u1.t1_id = u2.t1_t1_id;
Materialized view created.

SQL> alter materialized view mv_test 
2 REFRESH FAST ON COMMIT;
Materialized view altered.


SQL> conn user2/user2
Connected.

SQL> update table2set sometext = 'Au revoir' where t2_id=1;
1 row updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-00942: table or view does not exist

Solution :

The owner of the materialized view is missing  the "SELECT ANY TABLE" SYSTEM privilege


Grant the owner of the materialized view the missing "SELECT ANY TABLE" SYSTEM privilege directly, 
and not through roles:
 


Example:
-------
   SQL> connect system/manager
   Connected.
   SQL> grant select any table to user1;
   Grant succeeded.

   SQL>  conn user2/user2
   Connected.
   SQL>  update table2  set sometext = 'Au revoir'  where t2_id=1;
   1 row updated.

   SQL> commit;
   Commit complete.

No comments:

Post a Comment