Migrating geometry from one storage type to another using ArcSDE commands

You can change the geometry storage used in a feature class in a geodatabase in Oracle or Microsoft SQL Server by using the migrate operation with the sdelayer command. The migrate operation changes the geometry storage of the feature class to the geometry storage type in the DBTUNE configuration keyword you specify with the –k option.

Caution:

It is important that the keyword be properly created to include the correct parameter and value. If you specify a keyword with incorrect or missing information, the information is read from the DEFAULTS keyword. For this reason, Esri recommends that you create a custom keyword specifically for the migration and make sure the keyword contains the parameter and value to which you are migrating the data, as well as a UI_TEXT parameter. The UI_TEXT parameter makes the keyword available for use to ArcGIS clients.

The syntax for the sdelayer command with the migrate operation is as follows:

sdelayer –o migrate –l <table,column> –k <config_keyword> 
[–i {<service> | <direct_connect>}] [–s <server>] -D [<database_name>]
–u <user_name> [–p <user_password>] [–N] [–q] 

Use the options as follows:

Option	Description
–D	Specify the name of the database with this option. This option is not used for Oracle databases.
–o	This option indicates the operation being performed; in this case, migrate.
–l	Use this option to specify the name of the business table of the feature class and the name of the shape column for which you are migrating the geometry type. The two names are separated by a comma (no spaces).
–k	This option designates the DBTUNE configuration keyword for the geometry storage type to which you are migrating the feature class. The keyword must contain a GEOMETRY_STORAGE parameter set to either SDELOB or ST_GEOMETRY in Oracle databases or GEOMETRY or GEOGRAPHY in SQL Server databases.
–i	Provide either your direct connection syntax or the port number or name of the ArcSDE service you use to connect to the geodatabase. See Direct connections from ArcSDE commands to a geodatabase in Oracle or Direct connections from ArcSDE commands to a geodatabase in SQL Server for direct connection syntax.
–s	Specify the name of the server on which the database resides.
–u	Specify the user name of the feature class owner to log into the database.
–p	Specify the password of the user.
–N	Specify this option if you want to suppress the prompt to confirm the operation.
–q	Specify this option if you want to suppress all titles and warnings.

You will receive an error message when you execute the sdelayer command with the migrate operation if any of the following are true:

• The data is stored in basic precision.
• The user specified with –u is not the table owner.
• The existing storage type of the feature class and the specified DBTUNE keyword are identical. (In other words, the table is already using the storage type you specified with the keyword; therefore, no migration is necessary.)
• The business table does not contain an object ID field.
• You try to use the migrate operation in an ArcSDE geodatabase previous to 9.2.
• You try to use the migrate operation in a DBMS other than Oracle or SQL Server.
• You try to migrate an SDO_Geometry feature class that contains 3D features. Since Oracle Spatial does not support generating well-known binary or well-known text from 3D Oracle spatial geometry, this data cannot be migrated using the sdelayer command.

As indicated in the first bullet point, you must first migrate the feature class from basic to high precision before you can migrate the geometry storage. This can be done using the sdelayer command with the alter operation or the Upgrade Spatial Reference geoprocessing tool.

Note:

When migrating data storage types in an Oracle database using the migrate operation, a new segment is created in the database to which the data is copied. Once the migration is complete, the metadata is pointed again to the new segment and the old one is deleted. That means that at one point during the migration, there are two copies of the data. Therefore, the database must have enough space to store two copies.

Steps:

1. Create a backup of the data to be migrated.

   Creating a backup copy of the data you migrate ensures that, if migration fails for any reason, you still have your original data.

2. Open a DOS command prompt (Windows) or shell (UNIX or Linux).
3. Execute the sdelayer command with the migrate operation. For example:

   sdelayer –o migrate –l colpts,geometry –k ST_GEOMETRY 
   –i 68910 –s mainserver –u bjar –p mine.all.min

   Be sure to specify a configuration keyword that contains the GEOMETRY_STORAGE value to which you want to convert your data.