drop user 报错：ORA-00604、ORA-01000

    今天在测试库上折腾还原数据库，由于导入几次都失败了，就不停的drop user，然后create user操作，最后突然报错，说不能删除用户了，网上查了很多别人的经验，终于搞定了，在此记录一下，算是总结教训，也给大家类似问题的一个参考。

 

SQL> drop user test cascade;
drop user test cascade
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-01000: 超出打开游标的最大数

 

第一种方法，采用10046事件来分析原因。

1、做10046 trace跟踪。

 

SQL> alter session set events '10046 trace name context forever,level 12';

会话已更改。

SQL> drop user mvs cascade;
drop user mvs cascade
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-01000: 超出打开游标的最大数

SQL> alter session set events '10046 trace name context off';

会话已更改。

 

2、tkprof  trace文件
tkprof 说明：
(1)tkprof 是oracle 自带的一种文件格式化工具,tkprof是用来解释trace文件内容，把原始的trace文件转化为容易理解的文件。
(2)使用方法：在操作系统命令行下直接敲：tkprof.你能看到他的详细说明。
   使用方法格式：tkprof trace文件名 报告文件名
举例：
c:\>tkprof $oracle_home\admin\testdb\udump\oracledb_ora_3340.trc  c:\cms5_droptable.txt

 

3、查看错误原因（格式化后文件内容会很大，你只需要搜索错误信息就可以了。）如下就是我搜索的到错误信息：

********************************************************************************

The following statements encountered a error during parse:

SELECT USER_ID FROM ALL_USERS WHERE USERNAME = :B1

Error encountered: ORA-00604
--------------------------------------------------------------------------------
select count(*) from mdsys.rdf_rulebase$ where wner = :

Error encountered: ORA-00942
--------------------------------------------------------------------------------
select queue_name from "_DBA_STREAMS_QUEUES" where queue_owner=:

Error encountered: ORA-00604
********************************************************************************

分别运行了上面的语句，发现报错：mdsys.rdf_rulebase$ 表或视图不存在，听说这个mdsys系统自带用户可以删除，我就打算直接删除它，结果还是和前面删除用户报相同的错误，看来不是这里的问题。

 

第二种方法，就是清空shared_pool，这招在正在使用的正式系统上千万不要尝试。
SQL> select count(0) from v$open_cursor ;

  COUNT(0)
----------
       143
SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

SQL> alter system flush SHARED_POOL;

系统已更改。

SQL> select count(0) from v$open_cursor ;

  COUNT(0)
----------
       142
SQL> drop user test cascade;
 drop user test cascade
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-01000: 超出打开游标的最大数

 

第三种方法，大幅度增加open_cursors参数的值，再不行我就没招了。
SQL> alter system set open_cursors=1000;

系统已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             234884096 bytes
Database Buffers          956301312 bytes
Redo Buffers               14753792 bytes
数据库装载完毕。
数据库已经打开。
SQL> drop user test cascade;

用户已删除。

至此，用户终于删除成功了。

 

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

转载于:http://blog.itpub.net/9399028/viewspace-687674/