How To: Migrate ArcSDE 8.0.x Oracle Spatial layers to ArcSDE 8.1.x
ArcSDE 8.0.x introduced the ability to create layers with Oracle Spatial data types. The schema used to create these layers was similar to the ArcSDE compressed binary schema, including a feature table separate from the business (attribute) table. The main difference in the two schemas was that ArcSDE compressed binary included a BLOB column in the feature table to store the spatial data, while the Oracle Spatial data was stored in an SDE_GEOMETRY column in the feature table.
ArcSDE 8.1.x eliminated the feature table for layers based on Oracle Spatial tables. The SDO_GEOMETRY column is stored directly in the business table. This allows most third-party Oracle Spatial tables to be registered as ArcSDE layers.
While sdesetupora8i does a significant portion of the work upgrading from one release of ArcSDE to another, it does not upgrade ArcSDE 8.0.x layers with an SDO_GEOMETRY column in the feature table. This operation can be done manually, in one of two ways.
The first and easiest method is to use sdeexport to export your layers into ArcSDE export files, delete the layers, upgrade the ArcSDE server, and import your layers with sdeimport. This process will automatically create a spatial index and insert the necessary Oracle Spatial metadata for you.
The second and more complicated method is to use SQL to create new ArcSDE 8.1.x layers from ArcSDE 8.0.x layers. This method is described below. It uses an example, in which a new table (NEWLYR) is created from the tables that comprised the ArcSDE 8.0.x layer (OLDLYR and its feature table, F56). For specific information about the SQL commands, please refer to the Oracle Spatial User Guide and Reference.
1. To determine the actual name of the feature table for a particular layer, append to the letter F the LAYER_ID number from the SDE.LAYERS table. In the following case, the feature table for the layer OLDLYR is named F56.
SQL> SELECT LAYER_ID
2 FROM SDE.LAYERS
3 WHERE TABLE_NAME = ‘OLDLYR’
4 AND OWNER = ‘MYUSERNAME’;
1 row selected.
2. To create the new layer from the old layer, create a new table by selecting the attribute columns, all but the geometry column, from the old layer and the POINTS column from the old layer’s feature table.
SQL> DESCRIBE OLDLYR;
Name Null? Type
SQL> CREATE TABLE NEWLYR AS
2 SELECT A.NAME, A.FIPS, B.POINTS
3 FROM OLDLYR A, F56 B
4 WHERE A.SHAPE = B.FID;
SQL> DESCRIBE NEWLYR;
Name Null? Type
3. Insert the required metadata for the table into USER_SDO_GEOM_METADATA. The following example needs to be modified to fit your particular needs.
SQL> INSERT INTO USER_SDO_GEOM_METADATA
2 VALUES (
6 MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
7 MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005) ),
4. Create a new spatial index for the table. You can do this with Oracle's Spatial Index Advisor, or you can use SQL.
5. Connect to the database with an ArcSDE client and request a list of layers. The new layer will be auto-registered.
Be sure to back up your Oracle database before deleting any tables, in case something goes wrong and you have to restore the database to its original state.