oracle 删除 用户失败,oracle删除用户失败

数据库版本oracle10.2.0.1 ，用户无法删除

SQL> drop user mr cascade;

drop user mr cascade

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

打开跟踪：

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

运行删除用户：drop user MR cascade

关闭跟踪：

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

在服务器端/oracle/admin/orcl/udump目录下可看到trace文件

用tkprof orcl2_ora_561298.trc 文件名 分析到文件，打开该文件可看到：

The following statement encountered a error during parse:

delete from system.aq$_internet_agent_privs WHERE db_username = NLS_UPPER(:1

Error encountered: ORA-00942

查metlink文档，其提供的解决方案如下：

Cause

The table system.aq$_internet_agent_privs is missing.

Solution

To implement the solution, execute the following steps:

1.Check if system.aq$_internet_agent_privs exist.

SQL> conn / as sysdba

SQL> desc system.aq$_internet_agent_privs

2.Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

SQL> select default_tablespace from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE

------------------------------

SYSTEM

3.If system.aq$_internet_agent_privs does not exist, run $ORACLE_HOME/rdbms/admin/catqueue.sql

script manually logged in as 'SYS AS SYSDBA'. This will create the system.aq$_internet_agent_privs

table

SQL> conn / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql

SQL> exit

4.Confirm that system.aq$_internet_agent_privs is created properly:

SQL> desc system.aq$_internet_agent_privs

Name Null? Type

----------- -------- ------------

AGENT_NAME NOT NULL VARCHAR2(30)

DB_USERNAME NOT NULL VARCHAR2(30)

5.Then execute the DROP USER command again.

运行该脚本后，问题解决

但运行后发现exp有问题：报ora－01403 ora－06512

以下是解决办法：

$sqlplus "/as sysdba"

SQL> startup upgrade

SQL > @?/rdbms/admin/catalog.sql

SQL > @?/rdbms/admin/catproc.sql

SQL > @?/rdbms/admin/utlrp.sql