1.6 Import and Export Considerations

Workspace Manager supports the import and export of version-enabled tables in any of the following ways: a full database import and export, an import and export that only includes the schemas required by Workspace Manager, or a workspace-level import and export through Workspace Manager procedures.

No other export modes, such as single schema, table, or partition level, are currently supported.

Full database import and export operations can be performed on version-enabled databases using the Oracle utilities; however, the following considerations and restrictions apply:

• A database with version-enabled tables can be exported to another Oracle AI 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).

• For an import operation using the Oracle Data Pump Import utility, if the dump file includes the WMSYS schema, you must specify table_exists_action=truncate. If the dump file does not include the WMSYS schema, you can specify table_exists_action=append if the version-enabled tables being imported do not yet exist or are empty. (In general, dump files generated by Oracle Database release 10.2 or later will not include the WMSYS schema, while dump files generated by earlier releases will include the WMSYS schema.)

  The dump files must be from compatible versions of Workspace Manager. In general, any dump file created with VERSION=12 is capable of being supported.

• If you are using Data Pump Import, the dump file must have been created using Data Pump Export.

• The REMAP_SCHEMA capability in Data Pump Import utility is not supported with version-enabled databases.

• Workspace Manager no longer supports using the original Import and Export utilities for this mode.

Do not use the SYS schema when performing Workspace Manager import or export operations.

You can perform a limited (as opposed to full) export and import that includes all schemas related to version-enabled tables and workspaces, as well as any Workspace Manager metadata, but excludes all other schemas, as follows:

1. Call the Export_Schemas procedure to generate a dump file with the necessary objects and data.

2. Call the Import_Schemas procedure. (As with a full database import, Workspace Manager must already be installed and there must be no existing version-enabled tables or workspaces other than the LIVE workspace.)

For workspace-level export operations, each version-enabled table can be exported at the workspace level. Follow these steps to export a version-enabled table from one database into another database:

1. Call the Export procedure to store all of the data that needs to be exported into a staging table (for example, t1). The data that is exported can either be all of the data as seen from a particular workspace, savepoint, or instant, or only the data that was modified in the particular workspace. See the information about the Export procedure in DBMS_WM Package: Reference for more details.

   Note:

   Tables with valid time support (that is, with a column named WM_VALID of type WM_PERIOD) are not supported with the Export procedure. (Valid time support is explained in Workspace Manager Valid Time Support.)

   To export multiple workspaces for a version-enabled table, call the Export procedure again, specifying the new workspace that needs to be exported as well as the original staging table. If you intend to import the data into a non-versioned table, specify the versioned_db parameter as FALSE.

2. Export the staging table (for example, t1), using the Oracle Data Pump Export utility or the original Export utility.

3. Import the staging table (for example, t1), using the Oracle Data Pump Import utility or the original Import utility, into the destination database.

4. If you are importing into a version-enabled table, call the Import procedure to move the data from the staging table to the version-enabled table, specifying the workspace where the data resided on the source database and the workspace into which the data should be stored.

   The structure of the staging table must match that of the version-enabled table. By default, all enabled constraints must be validated before the import procedure successfully completes.

Note:

For exporting or importing version-enabled topologies, see also the Usage Notes for the relevant DBMS_WM procedures, including Export_Schemas and Initialize_After_Import.