djguvnor
asked on
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\
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'
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
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\
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'
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
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).
ASKER
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.
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.
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.