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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
djguvnorAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.