by Jeff Stonacek, Principal Architect
Oracle Spatial and Graph is a product that allows for the storage and processing of geospatial objects inside of the database. From the Oracle Technology Global Pricing List, “Oracle Spatial is an integrated set of functions and procedures that enables spatial data to be stored, accessed, and analyzed quickly and efficiently in an Oracle database.”
Oracle Spatial and Graph is a separately licensed feature, similar to Partitioning or Advanced Compression. From the Oracle Technology Price List:
The Spatial and Graph option lists for $17,500 per processor license and $350 per Named User Plus (NUP) license. Prices may change, so review the online price list from Oracle for accuracy.
Spatial vs. Locator
The Oracle Standard Edition (SE) database comes with a set of geospatial features called Locator. From the Oracle 12c Spatial and Graph Developer’s Guide, “Oracle Locator (also referred to as Locator) is a feature of Oracle Database Standard Edition. Locator provides core features and services available in Oracle Spatial and Graph. It provides significant capabilities typically required to support Internet and wireless service-based applications and partner-based GIS solutions.”
The following is a list of database code that demonstrates using Locator with an Oracle database.
Adding the column to the database table:
EXECUTE IMMEDIATE 'ALTER TABLE "TEST_LOC" ADD "LOC" "MDSYS"."SDO_GEOMETRY"';
Adding the column to the geometry metadata so that locator knows it exists:
Select count(*) into matching_row from USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_LOC' and COLUMN_NAME = 'LOC'; if (matching_row = 0) then EXECUTE IMMEDIATE 'INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES(''TEST_LOC'', ''LOC'', SDO_DIM_ARRAY(SDO_DIM_ELEMENT(''LONG'', -180, 190, .5), SDO_DIM_ELEMENT(''LAT'', -90, 90, .5)), 8307)'; end if;
Adding an index for the new column:
EXECUTE IMMEDIATE 'CREATE INDEX "TEST_LOC_IDX" ON "TEST_LOC" ("LOC") INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS (''layer_gtype=POINT'')';
The Oracle Spatial Developer’s Guide for Oracle database 11.2.0, lists what features require licensing of the Spatial and Graph add-on option. Appendix B of the Oracle Spatial Developer’s Guide lists the features that must be licensed with Spatial and Graph.
Appendix B in the Oracle 12c guide, also lists the features that are included with Locator versus the paid features with Spatial.
The following query is an example of how to identify geospatial objects stored in an Oracle database.
select * from mdsys.sdo_geom_metadata_table where sdo_owner not in ('MDSYS','OE');
The problem is that this query identifies all geospatial objects for both Locator and Spatial and Graph. This has the potential to flag false positives for licensed features if you are using only Locator, and not Spatial and Graph. If this happens during an audit, you can explain that only Locator is in use and therefore Spatial and Graph does not need to be licensed.
There is a way to install only Locator in an Oracle EE or SE database while leaving Spatial and Graph uninstalled. This is the recommended configuration if Spatial and Graph is not licensed, and will eliminate the possibility of running unlicensed features.
Install from Scratch
The cleanest way to run Locator without Spatial and Graph is to install Locator manually. To do this, uncheck Spatial from the list of options when creating a new database. Then run the following steps to install locator without Spatial and Graph.
Install JVM (if not installed):
@?/javavm/install/initjvm.sql @?/xdk/admin/initxml.sql @?/xdk/admin/xmlja.sql @?/rdbms/admin/catjava.sql @?/rdbms/admin/catexf.sql
Install Multimedia (if not installed):
@?/ord/admin/ordinst.sql SYSAUX SYSAUX @?/ord/im/admin/catim.sql
@?/md/admin/mdprivs.sql alter user mdsys account unlock; alter user mdsys identified by <pw>; connect mdsys/<pw> @?/md/admin/catmdloc connect / as sysdba alter user mdsys account lock password expire;
Remove Spatial and Graph
Spatial and Graph can also be removed from an existing Oracle EE database where it has been installed. This process leaves Locator functions in place, but removes the Spatial and Graph features and can be accomplished with the following command.
This command uninstalls the Spatial and Graph functions. Querying DBA_REGISTRY after running this script shows that Spatial has been removed.
col comp_name format a40 select comp_name, status from dba_registry;
COMP_NAME STATUS ---------------------------------------- ----------- Spatial REMOVED Oracle Multimedia VALID Oracle Expression Filter VALID Oracle XML Database VALID Oracle Workspace Manager VALID Oracle Database Catalog Views VALID Oracle Database Packages and Types VALID JServer JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID
In this blog we have tried to make sense of the difference between Locator, the free add-on to Oracle Database Enterprise and Standard Editions, and Spatial and Graph, which is a paid feature of Enterprise Edition only. This can cause confusion during an audit, so work with your application and development teams to know exactly what features are being used.