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

zhanglei_itput發表於2011-05-24
AIError

今天同事遇到一個問題，他在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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章