Dropping a User Results in ORA-942 against SDO_GEOM_METADATA_TABLE (Doc ID 303975.1)

Last updated on JUNE 09, 2017

Applies to:

Oracle Spatial and Graph - Version 9.2.0.1 to 12.1.0.2 [Release 9.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 04-Dec-2013***

Symptoms

When attempting to drop a user, the following error may be generated

SQL> DROP USER sysman CASCADE
*
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


The user being dropped should not own any Oracle Spatial objects.

Oracle Spatial may or may not be installed

Running the "drop user" command with following trace:

alter session set events '942 trace name ERRORSTACK level 3';


will show which statement is generating the error.  

There can be two possible SQL statements that could generate the error:

ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
DELETE FROM SDO_GEOM_METADATA_TABLE  WHERE '"'||SDO_OWNER||'"' = '"SYSMAN"'


or

 

select rowid from XDB.XDB$SCHEMA s where s.xmldata.schema_url = :1 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms