今天需要删除某些没用的表,但是在删除的时候确报一下错误:
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/