I need to move an application from 8i to 9i. Both the source and target instances reside on the same server. The source application uses Oracle Spatial & Workspace Manager. The target already hosts other spatial applications. How should I approach this? I'm particularly concerned with the metadata tables that Oracle Spatial and Workspace Manager maintain. For example: wmsys.wm$versioned_tables, wmsys.wm$udtrig_indo, mdsys.sdo_geom_metadata_table, mdsys.sdo_index_metadata_table
I have not worked with Oracle Spatial very much, but I think that what you should be able to export/import spatial data successfully. Metalink note 224309.1: "Export/Import of Spatial Objects and Known Issues" details some known bugs. I'd recommend applying the latest 8i patchset to your source database and the latest patchset available for your 9i release to the destination database before attempting any export/import tests. That should help resolve as many of the bugs as possible.
As for the workspace manager, in the 9.2 Application Developer's Guide - Workspace Manager, section 1.5 reads: =========copied from documentation Standard Oracle database import and export operations can be performed on version-enabled databases; however, the following considerations and restrictions apply:
-- A database with version-enabled tables can be exported to another Oracle database only if the other database has Workspace Manager installed and does not currently have any version-enabled tables or workspaces (that is, other than the LIVE workspace).
-- Only database-wide import and export operations are supported for version-enabled databases. No other export modes (such as schema, table, partition, and workspace) are supported.
-- For an import operation, you must specify IGNORE=Y.
-- The FROMUSER and TOUSER capabilities of the Oracle9i Import utility are not supported with version-enabled databases. =========end of copied from documentation.
Dig Deeper on Oracle database installation, upgrades and patches