Oracle生产库报无法truncate某张表，但是可以正常select该表，错误代码：ORA-00604 error occurred at recursive SQL level stringORA-00942 table or view does not exist
登录至该库，检查表结构及表空间均正常，检查alert.log也没有异常，使用sql_trace找到问题。
模拟错误环境：
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Apr 19 08:53:56 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn custom/mondec1Connected.SQL> alter session set sql_trace=true;Session altered.
SQL> truncate table zf_test2;truncate table zf_test2 *ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not exist
查看trc文件
[oracle@redhat udump]$ tail ora9irh_ora_17034.trcSTAT #8 id=2 cnt=0 pid=1 pos=1 obj=171 op='TABLE ACCESS CLUSTER SLOG$'STAT #8 id=3 cnt=1 pid=2 pos=1 obj=169 op='INDEX UNIQUE SCAN I_MLOG#'=====================PARSE ERROR #8:len=38 dep=1 uid=0 oct=7 lid=0 tim=1118562580893969 err=942delete from "CUSTOM"."MLOG$_ZF_TEST2"EXEC #1:c=15998,e=22698,p=0,cr=62,cu=8,mis=0,r=0,dep=0,og=4,tim=1118562580901281ERROR #1:err=604 tim=2871658588STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE'STAT #4 id=2 cnt=2 pid=1 pos=1 obj=170 op='TABLE ACCESS CLUSTER OBJ#(170)'STAT #4 id=3 cnt=2 pid=2 pos=1 obj=169 op='INDEX UNIQUE SCAN OBJ#(169)'
该表以前为高级复制的主表，后来取消高级复制后，出现异常。
SQL> select * from dba_objects a where a.object_name=upper('mlog$_zf_test2');no rows selected
SQL> select LOG_OWNER,MASTER,LOG_TABLE from dba_mview_logs;LOG_OWNER MASTER LOG_TABLE————— ————— ——————–CUSTOM ZF_TEST2 MLOG$_ZF_TEST2
问题就在这里，在取消高级复制时没有使用drop MATERIALIZED VIEW LOG ON zf_test2;而简单的drop table mlog$_zf_test2，导致问题出现。
解决：
SQL> drop MATERIALIZED VIEW LOG ON zf_test2;Materialized view log dropped.
SQL> truncate table zf_test2;Table truncated.
作者: sopher | 可以转载, 转载时务必以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.color-cc.com/2006/04/truncate.html
Comments (1)
1
sopher (Web)
Posted on April 17, 2006 17:04
:em17: 别人的错误就是我的教训
