Oracle数据库用户无法删除的处理案例
删除用户提示信息
SQL> drop user jzfp cascade;
drop user jzfp cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01422: exact fetch returns more than requested number of rows
从错误提示信息可以看到,是sql递归出现多值条件或者重写sql语句。是由于执行一条SQL语句到导致,因此我们可以跟踪一下这个SQL语句的执行过程,希望可以得到一些蛛丝马迹。
以下是跟踪过程:
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set events'10046 trace name context forever,level 4';
Session altered.
SQL> drop user jzfp cascade;
drop user jzfp cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01422: exact fetch returns more than requested number of rows
SQL> alter session set sql_trace-false;
alter session set sql_trace-false
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> alter session set sql_trace=false;
Session altered.
跟踪文件文件目录:
/u01/app/oracle/diag/rdbms/jzfpysdb/jzfpysdb1/trace/jzfpysdb1_ora_25323.trc
这个目录可以根据user_dump_dest参数确定。
查看跟踪产生的trace文件发现在删除过程中有如下信息,注意表黑的语句:
=====================
PARSING IN CURSOR #140007753306960 len=61 dep=1 uid=0 oct=12 lid=0 tim=1471928680661235 hv=3312064677 ad='7f56186f5838' sqlid='4d8b5vg2qn655'
drop table "JZFP"."AH02_2014" cascade constraints purge force
END OF STMT
PARSE #140007753306960:c=0,e=162,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1471928680661235
PARSE #140007751881456:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=0,tim=1471928680661387
BINDS #140007748108528:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f561818c1f0 bln=32 avl=04 flg=05
value="JZFP"
Bind#1
oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f56184df238 bln=32 avl=09 flg=09
value="AH02_2014"
Bind#2
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f56184dfa68 bln=32 avl=21 flg=09
value="CHECKPRIVRLS_SELECTPF"
EXEC #140007748108528:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1049820942,tim=1471928680661567
FETCH #140007748108528:c=0,e=48,p=0,cr=8,cu=0,mis=0,r=1,dep=3,og=1,plh=1049820942,tim=1471928680661636
CLOSE #140007748108528:c=0,e=1,dep=3,type=3,tim=1471928680661664
BINDS #140007748108528:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f561818c1f0 bln=32 avl=04 flg=05
value="JZFP"
Bind#1
oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f56184df238 bln=32 avl=09 flg=09
value="AH02_2014"
Bind#2
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7f56184dfa68 bln=32 avl=24 flg=09
value="CHECKPRIVRLS_SELECTPROPF"
EXEC #140007748108528:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=1,plh=1049820942,tim=1471928680661775
FETCH #140007748108528:c=0,e=19,p=0,cr=8,cu=0,mis=0,r=1,dep=3,og=1,plh=1049820942,tim=1471928680661803
CLOSE #140007748108528:c=0,e=1,dep=3,type=3,tim=1471928680661824
EXEC #140007751881456:c=1000,e=431,p=0,cr=16,cu=0,mis=0,r=1,dep=2,og=4,plh=0,tim=1471928680661837
CLOSE #140007751881456:c=0,e=9,dep=2,type=3,tim=1471928680661875
PARSE #140007749758088:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1471928680661930