之前基於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 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.   REFERENCES   相關內容     產品    >  >  >  >  >    關鍵字   DBA_TABLES;DELETE;DROP;MATERIALIZED VIEW;VIEW   錯誤   ORA-00942;ORA-942