drop物化视图log表导致insert、delete、update报ORA-00942

最新推荐文章于 2022-11-25 09:27:05 发布

cuizeen198835

最新推荐文章于 2022-11-25 09:27:05 发布

阅读量213

点赞数

文章标签：数据库

之前基于emp表测试物化视图创建了物化视图log表，测试完成后清理不用的表直接drop table 物化视图log表，过段时间再用emp表的时候发现：
select emp正常，但是insert、delete、update就报 ORA-00942: table or view does not exist

下面重现这个问题：
#创建物化视图log表在emp上
SQL> create materialized view log on emp with rowid;
Materialized view log created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
MLOG$_EMP TABLE
SALGRADE TABLE

#直接删除log表
SQL> drop table MLOG$_EMP purge;
Table dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

#进行select、insert、update、delete操作：
SQL> select count(*) from emp;

COUNT(*)
----------
14

#发现 insert、delete、update报ORA-00942
SQL> update emp set empno=empno+1;
update emp set empno=empno+1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> delete emp where empno=7379;
delete emp where empno=7379
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into emp(empno) values(1);
insert into emp(empno) values(1)
*
ERROR at line 1:
ORA-00942: table or view does not exist

解决方法：
#查出dba_mview_logs中存在，而的log表不存在的对象
select 'drop materialized view log on '||mvl.log_owner||'.'||mvl.master||';' cmd from dba_mview_logs mvl where not exists ( select table_name from dba_tables tab where tab.owner = mvl.log_owner and tab.table_name = mvl.log_table ) ;
CMD
--------------------------------------------------------------------------------
drop materialized view log on SCOTT.EMP;
#执行正确的删除 materialized view log 语句
SQL> drop materialized view log on SCOTT.EMP;
Materialized view log dropped.

#问题解决
SQL> delete emp where empno=7379;

1 row deleted.

SQL> rollback；

Rollback complete.

参考mos文章：

ORA-00942 when deleting from an existing Table after dropping materialized view log table (文档 ID 1912363.1)

转到底部

修改时间:2014-8-4

类型:PROBLEM

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

SYMPTOMS

Delete from an existing table fails with ORA-942. The table exists in dba_objects view with valid status.

CAUSE

If a materialized view log table is dropped by a different method than "DROP MATERIALIZED VIEW LOG" it can cause DMLs over the source table to raise ORA-942 errors.

The call stack show the following functions :

kxtrcpy kntkca kntkcai kntclc

SOLUTION

Please use the below query to identify the Materialised view log:

                     SQL> select 'drop materialized view log on '||mvl.log_owner||'.'||mvl.master||';'  cmd  from dba_mview_logs mvl  where not exists  ( select table_name from dba_tables tab  where tab.owner = mvl.log_owner and tab.table_name = mvl.log_table  )  /  
                   

Sample output:

                     CMD -------------------------------------------------------------------------------- drop materialized view log on EDA_STD_REPORT_ADM.STATICREPORTS; 
                   

Drop the related materialized view log and then perform the other DML operation.