Oracle spatial not working

Hi there,

Our live server had problems with Oracle spatial where certain functions were not working correctly.  After following instructions from an Oracle engineer I uninstalled oracle intermedia and reinstalled it but now any table which refers to an sdo_geometry object is invalid.  This is causing major knock on effects as lots of views and procedures do not compile!

The procedure I followed was:

log in to sqlplus as sys as sysdba

SQL> @C:\oracle\product\11.1.0\db_1\ord\im\admin\imremov.sql
About to remove Oracle Multimedia.
Checking to see if anyone is using Oracle Multimedia.
Note - this script does not check for dependent products such as
Oracle Spatial.


PL/SQL procedure successfully completed.

Are you sure you want to remove Oracle Multimedia (Y/N): y
old   2:   IF (substr(UPPER('&remove_mm'),1,1) = 'Y') THEN
new   2:   IF (substr(UPPER('y'),1,1) = 'Y') THEN

PL/SQL procedure successfully completed.






PL/SQL procedure successfully completed.


Removing Oracle Multimedia

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


Session altered.


PL/SQL procedure successfully completed.


Session altered.


User dropped.


User dropped.


User dropped.


Role dropped.

SQL> DROP SYNONYM SDO_GEOMETRY;
DROP SYNONYM SDO_GEOMETRY
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


SQL> DROP USER MDSYS CASCADE;
DROP USER MDSYS CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04020: deadlock detected while trying to lock object SCAMBSLDF.TBLSITE


SQL> DROP USER MDSYS CASCADE;

User dropped.



******
NOW REINSTALL INTERMEDIA using db configuration assistant
******




SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS, OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME ='SDO_GEOMETRY';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS  OWNER
------------------- ------- ------------------------------
SDO_GEOMETRY
SYNONYM             VALID   PUBLIC

SDO_GEOMETRY
TYPE                VALID   MDSYS

SDO_GEOMETRY
TYPE BODY           VALID   MDSYS
LVL 1
djguvnorAsked:
Who is Participating?
 
djguvnorConnect With a Mentor Author Commented:
The problem has been resolved.  Thank you for your comments johnsmith1962 but they did not help to solve the problem.

It turns out that once the sdo_geometry object has been dropped all objects of that type will never work again.  To fix it we had to restore our backup to a different machine and export and import all the tables with a spatial column.
0
 
johnsmith1962Commented:
Make sure that alll the types are owned by mdsys, then make sure all privilges on the types are granted appropriately (maybe try granting to public).
0
 
djguvnorAuthor Commented:
but how do I know what the privs are supposed to be?  and what is the list of types that are supposed to exist?

I've been trying to run some of the oracle supplied scripts to fix thing such as sdopatch.sql but none has resolved the problem.
0
 
johnsmith1962Commented:
That's why I said try granting to public.  See if it helps to grant all privileges on the objects to public (may want to try and reproduce problem in test environment).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.