重命名查詢中使用的表時無效的物化視圖。ORA-00942和ORA-06512，oracle19c

我正在嘗試替換物化視圖使用的表并將其替換為同義詞。mview 變得無效，需要洗掉并重新創建以使其作業。有沒有其他方法可以在不洗掉 mview 的情況下修復它？

-- 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';

編輯 2021.11.01 13:02：類似的策略似乎適用于同義詞

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

uj5u.com熱心網友回復：

您不能，因為物件 ID 已更改。在您的情況下，除非您重新創建 MVIEW，否則同義詞將不起作用。即使COMPILE是物體化視圖也會將狀態更改為VALID但事務將不起作用。

這個演示只涵蓋了一個場景，你有一個表，一個物化視圖記錄在它上面，以及一個快速重繪 的物化視圖

演示（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

現在，讓我們洗掉MLog，重命名table并創建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.

重新創建日志后，物化視圖無效

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

編譯后

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

但是，如果我想插入

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

為什么 ？

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.

標籤：甲骨文 物化视图

上一篇：當所有行都正常時從表中檢索記錄

下一篇：Oracle串列計數-子選擇