invalid materialized view when renaming a table used in the its query. ORA-00942 and ORA-06512, orac-CodePudding

I am trying to replace a table used by a materialized view and replace it with a synonym. The mview becomes invalid and needs to be dropped and recreated to make it work. Is there any other way to fix the mview without dropping it?

-- create test for issue with materialized views
create table tab_20211101 (first_name varchar2(100) primary key );
insert into tab_20211101 (first_name) values ('abc');
commit;
create materialized view log on tab_20211101 with rowid, primary key including new values;
create materialized view tab_20211101_mv refresh fast on commit as select * from tab_20211101;
select * from tab_20211101_mv;

-- now rename the table
drop materialized view log on tab_20211101;
alter table tab_20211101 rename to tab_20211101_new_name;
create materialized view log on tab_20211101_new_name with rowid, primary key including new values;

-- materialized view is now invalid, makes sense
select * from USER_OBJECTS a where a.OBJECT_NAME like 'TAB_20211101%' and STATUS = 'INVALID';
create synonym tab_20211101 for tab_20211101_new_name;

-- mv query works
select * from tab_20211101
-- materialized view still invalid, hmmm
select * from USER_OBJECTS a where a.OBJECT_NAME like 'TAB_20211101%' and STATUS = 'INVALID';

/*
 cannot refresh with ugly error
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
 */
begin
    DBMS_MVIEW.REFRESH('TAB_20211101_MV');
end;

drop materialized view tab_20211101_mv;
create materialized view tab_20211101_mv refresh fast on commit as select * from tab_20211101;
-- mview is now valid
select * from USER_OBJECTS a where a.OBJECT_NAME like 'TAB_20211101%' and STATUS = 'INVALID';

edit 2021.11.01 13:02: a similar strategy seems to work for synonyms

create table tab_20211101 (a int primary key);
create synonym tab_20211101_syn for tab_20211101;
select * from tab_20211101_syn; -- OK
alter table tab_20211101 rename to tab_20211101_new;
select * from tab_20211101_syn; -- ORA-00980: synonym translation is no longer valid
create synonym tab_20211101 for tab_20211101_new;
select * from tab_20211101_syn; -- OK

CodePudding user response：

You can't, because object id has changed. In your case the synonym will not work unless you recreate the MVIEW. Even COMPILE the Materialized view will change the status to VALID but transactions will not work.

This demo only covers a scenario where you have a table, a materialized view log over it, and a materialized view with fast refresh

Demo ( Oracle 19c )

SQL> create table test.t2 ( c1 number, c2 number ) ;

Table created.

SQL> create materialized view log on test.t2 ;

Materialized view log created.

SQL> create materialized view test.mv_t2 refresh fast on commit as select * from test.t2 ;

Materialized view created.

SQL> select * from test.mv_t2 ;

        C1         C2
---------- ----------
         1          1
         2          2
         3          3

Now, let's drop the MLog, rename the table and create the synonym

SQL> drop materialized view log on test.t2 ;

Materialized view log dropped.

SQL> alter table test.t2 rename to t2_old ;

Table altered.

SQL> create or replace synonym test.t2 for test.t2_old ;

Synonym created.

After recreating the log, the materialized view is invalid

SQL> create materialized view log on t2_old ;

Materialized view log created.

SQL> select object_name , status from user_objects where object_name = 'MV_T2' ;

OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
MV_T2
VALID

MV_T2
INVALID

After compiling

SQL> alter materialized view test.mv_t2 compile ;

Materialized view altered.

SQL> select object_name , status from user_objects where object_name = 'MV_T2' ;

OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
MV_T2
VALID

But, if I want to insert

SQL>  insert into test.t2 values ( 4 ,4 ) ;

1 row created.

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

Why ?

Materialized view and synonym dependencies are based on the object id in the data dictionary, not the object name, so if you rebuild a source table, view, or synonym, you have no choice to rebuild the materialized view as well even if everything has the same names.