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

PS:也可参考老杨博客http://blog.itpub.net/4227/viewspace-617658/， 11.2.0.4 不可drop table 方式删除物化视图日志

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

ITPub博客

ORA-00942 when deleting from an existing Table

APPLIES TO:

SYMPTOMS

CAUSE

SOLUTION

最新文章