ITPub博客

首页 > Linux操作系统 > Linux操作系统 > "DROP USER" fails with error: ORA-00942 and ORA-00604 (一)

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

原创 Linux操作系统 作者:zhanglei_itput 时间:2011-05-24 14:15:00 0 删除 编辑

今天同事遇到一个问题,他在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博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-696140/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1132309