Fun with Spatial data

I'm working on some databases that use Spatial data - not the Spatial option, but Locator. Locator is the cut-down version of Spatial that doesn't need a licence. 

To install Locator:

1. Install Oracle JVM
    @?/javavm/install/initjvm.sql
    @?/rdbms/admin/catjava.sql
    @?/xdk/admin/initxml.sql
    @?/xdk/admin/xmlja.sql

2. Install Oracle XMLDB
    @?/rdbms/admin/catqm.sql SYSAUX TEMP NO

3. Install Oracle Multimedia
    @?/ord/admin/ordinst.sql SYSAUX SYSAUX
    @?/ord/im/admin/catim.sql

4. Check the install
    execute validate_ordim;
    select comp_name,status from dba_registry;

The application here has a check to see if Spatial is installed. If it doesn't find the entry in dba_registry, it exits. There is a way to install and de-install Spatial so that it shows up in dba_registry but is "REMOVED". For some reason there is an Oracle note on this - How To Deinstall Oracle Spatial Keeping Oracle Locator [ID 1070647.1]

In 11GR2, there are 2 scripts, one to install and one to remove:

     @?/md/admin/mdinst.sql
     @?/md/admin/mddins.sql

So, now it's been installed, here are some things to look out for.

You can't export / import spatial data.

The application needs to copy schemas around databases. But an export / import of spatial data fails. This is from the Oracle documentation : 

http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_index_query.htm#autoId6

4.1.6 Export and Import Considerations with Spatial Indexes and Data
If you use the Export utility to export tables with spatial data, the behavior of the operation depends on whether or not the spatial data has been spatially indexed:
If the spatial data has not been spatially indexed, the table data is exported. However, you must update the USER_SDO_GEOM_METADATA view with the appropriate information on the target system.
If the spatial data has been spatially indexed, the table data is exported, the appropriate information is inserted into the USER_SDO_GEOM_METADATA view on the target system, and the spatial index is built on the target system. However, if the insertion into the USER_SDO_GEOM_METADATA view fails (for example, if there is already a USER_SDO_GEOM_METADATA entry for the spatial layer), the spatial index is not built.
If you use the Import utility to import data that has been spatially indexed, the following considerations apply:
If the index on the exported data was created with a TABLESPACE clause and if the specified tablespace does not exist in the database at import time, the index is not built. (This is different from the behavior with other Oracle indexes, where the index is created in the user's default tablespace if the tablespace specified for the original index does not exist at import time.)

Which basically means you can't export / import spatial data.

This is a work-around:

1. Export the schema as normal.
2. Create the schema in the target database
3. Create a database link from the target database to the source database as the schema
4. Insert the data from the source into the target: 

    insert into user_sdo_geom_metadata select * from user_sdo_geom_metadata@database_link_name;

5. Commit
6. Perform the import.

However, I had a requirement to do this with 2 databases that weren't connected, so I couldn't create a database link. This meant jumping through some more hoops.

1. Create a temporary table in the source database (as system is fine, just drop it afterwards):

   create table geom_temp as select * from mdsys.sdo_geom_metadata_table where sdo_owner='XXXX';

  where XXXX is the schema name.

2. Export this table and get the dump file onto the target host somehow. Use your imagination.
3. Import the table into the target system schema.
4. Grant select on this table to the user schema.
5. Log into the database as the schema owner and then run the insert:
   
    insert into user_sdo_geom_metadata select SDO_TABLE_NAME,SDO_COLUMN_NAME,SDO_DIMINFO,SDO_SRID from system.geom_temp;

If you receive an error – ORA-13223: duplicate entry – delete the data first – delete from user_sdo_geom_metadata; - and run the insert again.


Another issue I had was that the spatial indexes didn't create because the source tablespace didn't exist in the target database - this is mentioned in the chunk of text from the documentation above. 

However, when I did a "select index_name from dba_indexes where index_name='YYYY';", it showed that it did exist. So I thought it was OK, but the user was getting this:

Caused by: java.sql.SQLException: ORA-13226: interface not supported without a spatial index

I had to drop and recreate the indexes.