drop user 报错ora-00604
问题描述：
SQL> show user
USER is "SYS"
SQL> drop user efmis_zhongyang cascade;
drop user efmis_zhongyang cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
排查问题思路：
sql_trace跟踪sql：
SQL> alter session set sql_trace=true;
Session altered.
SQL> drop user efmis_zhongyang cascade;
drop user efmis_zhongyang cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
SQL> alter session set sql_trace=false;
Session altered.
通过以下语句确认跟踪文件位置：
select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
p.spid || '.trc' trace_file_name
from (select p.spid
from sys.v$mystat m, sys.v$session s, sys.v$process p
where m.statistic# = 1
and s.sid = m.sid
and p.addr = s.paddr) p,
(select t.instance
from sys.v$thread t, sys.v$parameter v
where v.name = 'thread'
and (v.value = 0 or t.thread# = to_number(v.value))) i,
(select value from sys.v$parameter where name = 'user_dump_dest') d;
/u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15959.trc
查看跟踪文件有如下信息：
EXEC #46948114736296:c=5999,e=5848,p=0,cr=26,cu=3,mis=0,r=0,dep=1,og=4,plh=0,tim=1459587911014685
ERROR #46948114736296:err=38301 tim=1459587911014699
EXEC #46948114738488:c=56991,e=57337,p=0,cr=11776,cu=6,mis=0,r=0,dep=0,og=1,plh=0,tim=1459587911014743
ERROR #46948114738488:err=604 tim=1459587911014757
通过ERROR #46948114736296:err=38301可以得到oracle要执行语句为：
=====================
PARSING IN CURSOR #46948114736296 len=93 dep=1 uid=0 oct=12 lid=0 tim=1459587911008777 hv=2610238907 ad='2ab2f5b054a0' sqlid='byuwhpydta5dv'
drop table "EFMIS_ZHONGYANG"."BIN$LR983/P8HqDgUyQDqMBbSw==$0" cascade constraints purge force
END OF STMT
PARSE #46948114736296:c=0,e=114,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1459587911008776
=====================
可以从上看到现在删除用户efmis_zhongyang要删除的是回收站对象"EFMIS_ZHONGYANG"."BIN$LR983/P8HqDgUyQDqMBbSw==$0"
登录SQL> conn efmis_zhongyang/1
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$LR983/P8HqDgUyQDqMBbSw==$0 TABLE
