Uninstalling Oracle Spatial

[chrisplough]

If you've installed OTM v5.5 into an Oracle DB that has Oracle Spatial enabled, you'll find that certain functions (such as distance lookups) fail in OTM. In order to resolve these issues, you'll need to uninstall Oracle Spatial from your database using the following steps.

Login to your OTM DB as the sys user and run:

SQL> drop user MDSYS cascade;

SQL> set pagesize 0
SQL> set feed off
SQL> spool drop_spatial.sql
SQL> select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
SQL> spool off;
SQL> @drop_spatial.sql
SQL> commit;

Then:

• Open up a command prompt on the OTM App server
• Change into the <otm_install_dir>/glog/oracle/script8 directory
• Sqlplus into the OTM database as the glogowner user
• Run:

SQL> @create_public_synonyms.sql

To verify that everything has been completed successfully, run the following query:

SQL> select * from dba_synonyms where synonym_name='DISTANCE';

The TABLE_NAME should be DISTANCE and the TABLE_OWNER should be GLOGOWNER.

References:
Oracle Note: 179472.1
Oracle Note: 387793.1

Hope this helps!

Thanks!
--Chris