记一次ORA-00604/ORA-01422

今天需要删除某些没用的表，但是在删除的时候确报一下错误：

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

 

试着删除其他的表也发现如何，

于是跟踪下sql的执行情况：

PARSING IN CURSOR #2 len=523 dep=1 uid=46 ct=47 lid=46 tim=1544447922608808 hv=2706012741 ad='282567a4'
declare
   stmt varchar2(200);
   rdf_exception EXCEPTION;   pragma exception_init(rdf_exception, -20000);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       BEGIN
         EXECUTE IMMEDIATE
          'begin ' ||
            'mdsys.rdf_apis_internal.' ||
            'notify_drop_user( :don ); ' ||
          'end;'
             USING dbms_assert.QUALIFIED_SQL_NAME(dictionary_obj_name);
         EXCEPTION
           WHEN rdf_exception THEN RAISE;
           WHEN OTHERS        THEN NULL;
       END;
    end if;
end;
END OF STMT
PARSE #2:c=1000,e=975,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1544447922608314
EXEC #2:c=2000,e=1566,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=1544447922620138
=====================
PARSING IN CURSOR #7 len=58 dep=1 uid=46 ct=3 lid=46 tim=1544447922626848 hv=1385655277 ad='28251fd8'
select dummy from dual where  ora_dict_obj_type = 'TABLE'
END OF STMT
PARSE #7:c=4999,e=4727,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1544447922626763
EXEC #7:c=13998,e=12664,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1544447922643368
FETCH #7:c=5000,e=4478,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1544447922648902
EXEC #1:c=221967,e=220557,p=0,cr=159,cu=0,mis=0,r=0,dep=0,og=1,tim=1544447922652333
ERROR #1:err=604 tim=431002887
STAT #7 id=1 cnt=2 pid=0 pos=1 bj=0 p='FILTER  (cr=3 pr=0 pw=0 time=14072 us)'
STAT #7 id=2 cnt=2 pid=1 pos=1 bj=258 p='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=1166 us)'
*** 2020-02-12 21:38:04.986
=====================
PARSING IN CURSOR #6 len=54 dep=0 uid=0 ct=49 lid=0 tim=1544447934555326 hv=1869495724 ad='0

当执行到此时的时候递归的SQL就停止了，而且报错。

找到了报错的递归SQL为：

select dummy from dual where  ora_dict_obj_type = 'TABLE'

是访问dual虚拟表的时候的问题，

查询了一些资料，说dual表内数据部准确，

于是:

 

SQL> create table my_dual as select * from dual;
 
Table created.
 
SQL> select * from my_dual;
 
D
-
X
3
4
1
 

 

果然，

于是删除掉除X以外的所有记录，重新drop表则OK。

SQL> delete from dual where dummy = '1';
 
1 row deleted.
 
SQL> delete from dual where dummy = '3';
 
0 rows deleted.
 
SQL> delete from dual where dummy = '4';
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete from dual where dummy = '3';
 
1 rows deleted.
 
SQL> drop table test;
 
Table dropped.

 

也可以设置一下oracle的隐含参数：

Setting _system_trig_enabled=FALSE

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10130206/viewspace-666838/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10130206/viewspace-666838/