|Oracle Spatial User's Guide and Reference
Part Number A85337-01
This appendix provides information concerning installation, compatibility, and migration between various Oracle Spatial product releases.
Beginning with Spatial Data Option 7.3.3, all interfaces are supported in each subsequent release. A spatial application built for and using the 7.3.3 Spatial Data Option interfaces will work with a release 8.0.4, 8.0.5, 8.1.6, or 8.1.7 database server. The implementations of these interfaces have changed, and therefore PL/SQL packages from prior releases of the Spatial cartridge will not work with later releases of the Oracle8i database server. Therefore, you must upgrade both the database server and Spatial at the same time if you wish to use older spatial applications with an Oracle8i release of Spatial.
Spatial must always be synchronized with the Oracle8i database server on upgrade or downgrade. In both cases, Spatial must be reinstalled.
Spatial release 8.1 requires Oracle8i Enterprise Edition and the Objects Option. Spatial release 8.1 was redesigned to use various Oracle8i object and extensibility features. Many of the Spatial release 8.1 features depend on new features in release 8.1 of the database server. Therefore, there are many compatibility and migration issues that need to be addressed in this release of Spatial. This appendix outlines the database and application compatibility issues.
Database compatibility issues exist because Spatial uses extensible indexing and object types in release 8.1; and therefore if a release 8.1 database instance is downgraded to release 8.0.5, the spatial objects must be deleted and re-created. In this case, the data must be exported and imported into the release 8.0.5 database. This, and other requirements, result in application incompatibility. A release 8.1 Spatial application will likely use the new spatial operators and therefore will not work with a release 8.0.5 instance unless it can identify the Spatial version and dynamically change its spatial queries.
An upgrade or downgrade of the database server version requires a corresponding upgrade or downgrade of Spatial. If a release 8.0.5 database server is upgraded to release 8.1, Spatial must also be upgraded. The reason has to do with using dynamic SQL in PL/SQL and with invoker's rights in release 8.1. Similarly, if a release 8.1 server is downgraded, Spatial must be downgraded too. Lastly, if a release 8.1 server is running in release 8.0 compatibility mode, Spatial will experience various failures unless it is reconfigured for release 8.0.5. You can reconfigure the product by running the downgrade script: c813d805.sql.
To install Spatial, the script catmd.sql in the ORACLE_HOME/md/admin directory must be run as user MDSYS. The MDSYS user should be created with the set of privileges listed in ORACLE_HOME/MD/mdprivs.sql, and with both default and temporary tablespaces.
Installation of Spatial requires that the COMPATIBLE init.ora parameter is set to 18.104.22.168.0 or higher. This is required for the creation and definition of Spatial index types and operators. Thus, if the database was created with a compatibility parameter value of 8.0.n.n.n, the DBA must shut down the database and restart with COMPATIBLE=8.1.n.n.n.
If Spatial has been installed and the database compatibility needs to be reset to 8.0.n.n.n from 8.1.n.n.n, do the following:
After running ORACLE_HOME/MD/c813d805.sql, resetting the database compatibility to 8.1.n.n.n from 8.0.n.n.n requires running the script ORACLE_HOME/MD/c805u813.sql to reinstall and enable the object-relational implementation of Spatial.
All releases of the Spatial product provide a set of predefined spatial data types, topological operators such as RELATE, and a spatial indexing mechanism. The Oracle8i (release 8.1.5) Spatial release differed from pre-Oracle8i releases in that it:
All interfaces preceding Oracle8i are maintained, but the package bodies have been changed to use the preceding features. Thus, for Oracle8i, the Spatial packages must be reinstalled to use these interfaces even if the compatibility parameter is set to 8.0.
No data migration is needed, and the 7.3.4/8.0.4 spatial applications will work without modification. Any OCI-specific migration issues must be handled in the same manner as they would have to be for any OCI application.
The release 7.3.4/8.0.4 to 8.1.5 upgrade requirements are the same. Upgrade both Oracle8i and Spatial. Perform all the necessary steps for an upgrade. Your spatial applications will continue to work as before.
Downgrading from 8.0.5 or earlier releases to a previous release of the database server and Spatial requires no special steps specific to the Spatial implementation. However, this situation is different for Oracle8i. In Oracle8i, Spatial uses objects and extensible indexing. Therefore, it creates database objects specific to Oracle8i that are not compatible with previous releases of the database server. When you downgrade the database server and Spatial from Oracle8i to release 8.0.5, a spatial-specific downgrade script must be executed to remove all the spatial geometry type, index type, and spatial operator definitions.
Beginning with release 7.3.3, all subsequent releases can work with spatial data from previous releases. That is, no data migration is required. The situation is different in Oracle8i because Spatial now allows two storage mechanisms. If you want the features specific to Oracle8i, such as extensible indexing and spatial operators, you must migrate your spatial data from the release 7.3.3 columns-of-numbers style to the SDO_GEOMETRY storage scheme. Spatial provides a stored procedure and sample code that demonstrates one way of migrating data and metadata.
Migrating data on downgrades is more complex. Spatial provides OCI demonstration programs to read SDO_GEOMETRY instances and store them in a release 8.0.5 spatial schema for comparable data. The demo also addresses issues related to the changes in the way metadata is stored in Oracle8i compared to previous releases. The complexity arises from the following:
You cannot store arcs and circles in any release earlier than Oracle8i, and you cannot use data from a release 7.3.4 or later spatial layer in release 7.3.3 if it contains instances of type UNSUPPORTED_GEOMETRY (etype=0).
To migrate from Spatial release 8.1.5 to release 8.1.7, you must first migrate from release 8.1.5 to release 8.1.6, as described in Section B.5.1, and then migrate to release 8.1.7, as described in Section B.5.2. Be sure that you perform any actions needed for data migration and compatibility for release 8.1.6 before migrating to release 8.1.7.
To migrate from Spatial release 8.1.6 to release 8.1.7, run the c816u817.sql script, as described in Section B.5.2.
Spatial release 8.1.5 uses objects and index types to create spatial indexes. However, currently there is no tool to convert release 8.1.5 spatial indexes to the new format for release 8.1.6. Therefore, you must delete all the spatial indexes built in release 8.1.5 database and re-create them in a release 8.1.6 database.
Follow these steps to upgrade from release 8.1.5 to release 8.1.6 of Spatial:
To see which users have spatial indexes and the spatial index parameters, enter the following SQL statement:
SELECT PARAMETERS, INDEX_NAME FROM user_indexes, sdo_index_metadata_table WHERE INDEX_NAME = SDO_INDEX_NAME;
Save this information before upgrading to Spatial 8.1.6.
This script deletes all the spatial indexes and installs types and packages related to Spatial 8.1.6. It also migrates the data from all the SDO_GEOM_METADATA tables in each user's schema to the new xxx_SDO_GEOM_METADATA views managed by Spatial.
However, this metadata migration is done only for those layers that have a spatial index built on them. If you need to do this metadata migration separately, you can use the following SQL statement (once for each user with spatial data):
INSERT INTO USER_SDO_GEOM_METADATA SELECT TABLE_NAME, COLUMN_NAME, DIMINFO, NULL FROM SDO_GEOM_METADATA;
For example, if you connect as user Herman and execute that statement, it migrates all the metadata in user Herman's SDO_GEOM_METADATA table.
Spatial release 8.1.6 introduced new SDO_GTYPE and SDO_ETYPE values to better manage the geometry data. To take advantage of these benefits, it is strongly recommended that the data be migrated to the new SDO_GTYPE and SDO_ETYPE values.
To initiate this data migration, execute the SDO_MIGRATE.FROM_815_TO_81X procedure on each table that has geometry data. This procedure updates all the geometries to set the SDO_GTYPE and SDO_ETYPE values.
Spatial release 8.1.6 changed the way the geometry metadata is managed. In release 8.1.5, the metadata is managed by the users by keeping the metadata in SDO_GEOM_METADATA tables in each user's schema. In release 8.1.6, the metadata is centrally managed under the MDSYS schema, and the user can access and manipulate the metadata through metadata views.
A release 8.1.5 Spatial application will fail against a release 8.1.6 database if it tries to access the metadata. Therefore, if you need to run a release 8.1.5 application with a release 8.1.6 database, you need to keep the SDO_GEOM_METADATA table and make sure that the USER_SDO_GEOM_METADATA view and the SDO_GEOM_METADATA table are consistent all the time.
A recommended method for ensuring consistency is to create SQL triggers on the SDO_GEOM_METADATA table to perform a corresponding insert, update, or delete operation on USER_SDO_GEOM_METADATA for each such operation on SDO_GEOM_METADATA. For example, if user SCOTT has spatial data and needs to keep the SDO_GEOM_METADATA table consistent with the new metadata views, SCOTT can create a trigger (shown in Example B-1) that inserts data into the USER_SDO_GEOM_METADATA view whenever SCOTT inserts data into the SDO_GEOM_METADATA table.
CREATE TRIGGER scott_sdo_meta_ins_trig before insert on sdo_geom_metadata referencing new as n FOR EACH ROW BEGIN INSERT INTO user_sdo_geom_metadata VALUES(:n.table_name, :n.column_name, :n.diminfo, NULL); END; /
User SCOTT can create similar triggers for delete and update operations on the SDO_GEOM_METADATA table.
To migrate from Spatial release 8.1.6 to release 8.1.7, run the following script: ORACLE_HOME/md/c816u817.sql