今天同事遇到一个问题，他在drop table和drop user的时候都报了同一个错误：
SQL> drop user testb;
drop user testb
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

我还头一次遇到这个问题，本以为是应用程序导致的，但是后来想了想，那个是在drop user的时候才会报的错误。
metalink一查，找到了2篇文章，version不同，trace出来的内容也不同：

"DROP USER" fails with error: ORA-00942 and ORA-00604 [ID 269469.1]

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

修改时间 04-MAY-2010 类型 PROBLEM 状态 PUBLISHED

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

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 9.2.0.1 - Release: 8.1.5 to 9.2
Information in this document applies to any platform.

Symptoms
The command "DROP USER" fails with error: ORA-00942 and ORA-00604

1. For example:

SQL> drop user testb;
drop user testb
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

2. Cause
Table SDO_GEOM_METADATA_TABLE (part of Oracle Spatial) not present in the DB.

3. exec

This can be confirmed the doing the following:

alter session set sql_trace=true;

--OR--

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

drop user cascade;

alter session set sql_trace=false;

4. check trace file

The error tracefile will contain the failing statement.

For example:

ORA-00942: table or view does not exist
Current SQL statement for this session:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'TESTA' Solution

5. Run the script. catmd.sql
(located in $ORACLE_HOME/md/admin dir).
The catmd.sql script. is the script. that loads all objects needed by Oracle Spatial in the database. Then drop the user.

参考文献：

1. "DROP USER" fails with error: ORA-00942 and ORA-00604 [ID 269469.1]

2. Dropping a User Results in ORA-942 against SDO_GEOM_METADATA_TABLE [ID 303975.1]

ITPub博客

"DROP USER" fails with error: ORA-00942 and ORA-00604 (一)

最新文章