当重命名其查询中使用的表时，无效的物化视图。ORA-00942和ORA-06512，甲骨文19c
当重命名其查询中使用的表时，无效的物化视图。ORA-00942和ORA-06512，甲骨文19c
提问于 2021-11-01 10:52:43
我试图替换物化视图使用的表，并将其替换为同义词。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回答 1
Stack Overflow用户
回答已采纳
发布于 2021-11-01 11:41:27
你不能，因为对象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为什么？
物化视图和同义词依赖关系是基于数据字典中的对象id，而不是对象名称，因此，如果您重新生成源表、视图或同义词，那么您也没有选择重新生成物化视图，即使所有东西都有相同的名称。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69795873
复制相关文章
相似问题
腾讯云开发者
