ArcGIS Server Banner

Migrating Oracle data from one storage type to another

Migrating Oracle data from one storage type to another

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

About migrating Oracle data types

Beginning with Oracle 11g, LONG RAW data types are no longer supported. It is possible your current ArcSDE geodatabase in Oracle contains LONG RAW fields. You might have attribute columns, geometry columns, or columns in the raster side tables that use LONG RAW storage. The storage for these columns is controlled by the DBTUNE parameters ATTRIBUTE_BINARY, GEOMETRY_STORAGE, and RASTER_STORAGE respectively.

The defaults for these parameters under the DBTUNE DEFAULTS configuration keyword have changed. The following table shows the default setting under the DEFAULTS keyword in the DBTUNE table in ArcSDE 9.3, 9.2, and prior geodatabases.


Parameter Default at ArcGIS 9.3 Default at ArcGIS 9.2 Default prior to ArcGIS 9.2
ATTRIBUTE_BINARY BLOB BLOB LONG RAW
GEOMETRY_STORAGE ST_GEOMETRY LONG RAW (SDEBINARY) LONG RAW (SDEBINARY)
RASTER_STORAGE BLOB LONG RAW LONG RAW

Data created in new 9.3 geodatabases using the default parameter settings for ArcGIS 9.3 do not use the LONG RAW storage type. However, any existing data created with any or all of these parameters set to LONG RAW or any new data in upgraded geodatabases that have these parameters set to LONG RAW will still contain LONG RAW columns. To change the data types for these columns, you must alter your DBTUNE settings and migrate the data.

Beginning with ArcGIS 9.3, you can use the Migrate Storage geoprocessing tool, ArcSDE administration commands, ArcObjects, or the ArcSDE API to migrate existing LONG RAW columns to another data type. Additionally, you can migrate other geometry storage data types to ST_GEOMETRY storage. Possible migration paths include the following:


Parameter Migrate from/to
ATTRIBUTE_BINARY LONG RAW to BLOB
GEOMETRY_STORAGE LONG RAW (SDEBINARY) to BLOB (SDELOB)

LONG RAW to ST_GEOMETRY

BLOB to ST_GEOMETRY

SDO_GEOMETRY to ST_GEOMETRY
RASTER_STORAGE LONG RAW to BLOB

If the table was registered as versioned, migrating it to a different geometry storage type also updates the shape column in the Adds table. If the feature class has archiving enabled, the archive table's shape column is also updated.

The following conditions must be met before you convert your data:

Since both attribute and raster storage will be migrated to a BLOB data type and BLOB is an option for the conversion of geometries, it is recommended that you read the topic BLOB data storage in Oracle geodatabases before proceeding with the migration instructions.

The following sections describe how to migrate the attribute, geometry, or raster storage of a table in an ArcSDE geodatabase in Oracle.

How to migrate data types

Using the Migrate Storage geoprocessing tool

  1. Open the Migrate Storage tool. This can be found in the Data Management Tools toolbox in the Database set of tools.
  2. Browse to the location of the dataset you want to migrate. You can add multiple datasets.
  3. Choose a configuration keyword from the drop-down list. The configuration keyword must contain parameters set to one of the supported storage options.
  4. For example, if you are migrating the geometry of a LONG RAW feature class, the keyword you choose must have the GEOMETRY_STORAGE parameter set to SDELOB or ST_GEOMETRY.

    If you choose a keyword that contains a parameter set to an unsupported migration path, such as a GEOMETRY_STORAGE parameter set to SDO_GEOMETRY, the migration will fail.

  5. Click OK.
  6. Disconnect from the geodatabase and reconnect. This will refresh the shape column names, which may have changed when the storage type changed.

Tips

  • Remember, you must be the owner of the data to be able to migrate its geometry, raster, or attribute storage.
  • Be sure you have a backup copy of any data you intend to migrate. That way, if migration fails for any reason, you still have your original data.
  • For information on using geoprocessing scripts for the migration, see Migrate Storage (Data Management).

Using ArcSDE administration commands

Migrating geometry columns

You can change the geometry storage used in a feature class in a geodatabase in Oracle 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.

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

sdelayer –o migrate –l <table,column> –k <config_keyword> [–i <service>]
[–s <server_name>] –u <DB_user_name> [–p <DB_user_password>] [–N]

Where

–l specifies the name of the business table of the feature class and the shape column.

–k 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.

–i indicates the ArcSDE service or direct connection information.

–s defines the name of the server on which the database resides.

–u specifies the database user name of the feature class owner.

–p specifies the password of the user.

–N suppresses the prompt to confirm the operation.

You will receive an error message when you execute this command if any of the following is true:

Migrating attribute and other LONG RAW columns

You can migrate LONG RAW attribute columns to BLOB using the sdetable command with the migrate operation. This operation changes the storage of the attribute column from LONG RAW to BLOB by specifying a DBTUNE configuration keyword that has the ATTRIBUTE_BINARY parameter set to BLOB.

You can also use sdetable to migrate all the LONG RAW columns in a table at once. For example, if you have a feature class that is using LONG RAW raster storage and contains a binary attribute column of LONG RAW, when you use sdetable, both columns are converted based on the parameters set in the configuration keyword you specify with the –k option. As long as the specified configuration keyword contains both an ATTRIBUTE_BINARY parameter set to BLOB and a RASTER_STORAGE parameter set to BLOB, both column data types will be converted.

The syntax for sdetable –o migrate is as follows:

sdetable –o migrate –t <table> –k <config_keyword> [–i <service>]
[–s <server_name>] –u <user_name> [–p <user_password>] [–N] [–q]

Where

–t specifies the name of the table containing the column (or columns) to be migrated.

–k designates the DBTUNE configuration keyword for the storage type to which you are migrating.

–i indicates the ArcSDE service or direct connection information.

–s defines the name of the server on which the database resides.

–u specifies the database user name of the table owner.

–p specifies the password of the user.

–N suppresses the prompt to confirm the operation.

As with the other commands, the migration will fail if you are not the owner of the table, you try to migrate a table in a pre-9.3 geodatabase, or there are no valid parameters included with the specified configuration keyword.

Tip

  • Be sure you have a backup of copy of any data you intend to migrate. That way, if migration fails for any reason, you still have your original data.

See Also

  • Feature classes in a geodatabase in Oracle
  • Raster datasets and raster catalogs in a geodatabase stored in Oracle
  • ST_Geometry storage in Oracle
  • BLOB data storage in Oracle geodatabases