Dropping a User Results in ORA-942 against SDO_GEOM_METADATA_TABLE
Wednesday, April 11, 2012


Applies to:

Oracle Spatial - Version: 9.2.0.1 to 10.2.0.5 - Release: 9.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 13-Jan-2011***

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

Regardless of the SQL statement generating the error, the cause is still the same.
The Spatial objects were installed incorrectly and SYS or another user, other than MDSYS, owns the Spatial objects.
Specifically, the Spatial trigger, SDO_DROP_USER  is causing the problem.  When a user is dropped, the trigger will fire to determine if any Spatial objects are owned by that user.  If the tables that the trigger needs to query are owned by the wrong user, the trigger will fail and the user will not be dropped.
     

Solution

To implement the solution, please execute the following statement:
drop trigger SDO_DROP_USER   


which will allow the user to be dropped.
If there are other Spatial objects owned by SYS, or another user, that should be owned by MDSYS (objects beginning with SDO, MD, etc), then these objects should be dropped.  If Oracle Spatial is in the dba_registry, then is should be removed and re-installed. 
Note 413693.1 - Accidentally installed SPATIAL into SYS, SYSTEM or another Schema

at Wednesday, April 11, 2012  

Labels:

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)
Powered by Blogger.