Conversion workspaces

Conversion workspaces help you convert the schema and objects from your source database into the SQL syntax that is compatible with your destination database. This page provides an overview of Database Migration Service conversion workspaces:

Regardless of whether you use the automatic schema conversion or create an additional conversion mappings file, there are certain data types that are unsupported for Oracle migrations. For more information, see Known limitations for data types.

Automated code and schema conversion

When you create a conversion workspace, Database Migration Service automatically performs the initial schema conversion. Automatic schema conversion supports a very specific subset of available Oracle database objects.

Automated code conversion provides support for the following Oracle database objects:

Supported Oracle schema elements

  • Constraints
  • Indexes (only indexes which are created in the same schema as their table)
  • Materialized Views
  • Object Types (partial support)
  • Sequences
  • Synonyms
  • Tables
  • Views

Supported Oracle code elements

  • Triggers (table level only)
  • Packages
  • Functions
  • Stored Procedures

Interactive SQL editor

The interactive SQL editor lets you modify converted PostgreSQL syntax directly in Database Migration Service. You can use it to fix conversion issues or adjust the schema to better fit your needs. Some objects can't be modified in the built-in editor.

Editable Oracle objects

After you convert source database code and schema, you can use the interactive editor to modify the generated SQL for certain types of objects. The following Oracle objects are supported by the editor:

  • Table triggers (requires permission)
  • Materialized views
  • Packages
  • Functions, stored procedures
  • Synonyms
  • Views
  • Constraints
  • Indexes
  • Sequences

Additionally, some objects are converted but not available for editing directly inside Database Migration Service. To modify such objects, you need to perform the updates directly on the destination database after you apply the converted schema and code.

Objects that aren't supported for editing:

  • User-defined object types
  • Tables
  • Schemas

Gemini-assisted conversion features

Database Migration Service integrates Gemini for Google Cloud into conversion workspaces to help you speed up and improve the conversion process in two areas:

  • Provide code explainability features with the conversion assistant: a set of dedicated prompts that can help you better understand the conversion logic, propose fixes for conversion issues, or optimize converted code.

  • Expedite applying fixes for conversion issues with Gemini code conversion suggestions: a mechanism where the Gemini model can learn as you fix conversion issues and suggest changes to other faulty objects in the workspace.

For more information about Gemini-assisted conversion, see the following pages:

Conversion mapping files

You can customize the conversion logic with a conversion mapping file. The conversion mapping file is a text file contains precise instructions (referred to as conversion directives) for how your Oracle objects should be converted into PostgreSQL objects.

Supported conversion directives

Database Migration Service supports the following conversion directives for conversion mapping files:

EXPORT_SCHEMA

EXPORT_SCHEMA is a mandatory directive for all conversion mapping files. Database Migration Service requires this instruction to ensure that your source schemas are converted to the correct destination schemas. Make sure your conversion mapping files include this line: 

EXPORT_SCHEMA 1

SCHEMA

Database Migration Service must be able to determine which schema contains the objects that should be modified with your conversion directives. The SCHEMA directive results in the following adjustments to the conversion flow:

  • Database Migration Service converts only this schema. If you need to convert other schemas in a single conversion workspace, you need to upload multiple files with different schemas.
  • All other customization directives provided in your file apply only to objects in this particular schema.

Use the following format:

SCHEMA SCHEMA_NAME

Where SCHEMA_NAME is the name of your schema in the source database.

  • If you include this directive in the conversion mapping file, all customizations are applied only to objects contained in this specific schema. If you want to customize objects in other schemas, you need to create multiple conversion mapping files and upload them to the conversion workspace.
  • If you skip this directive, you must provide explicit schema names for objects modified by other conversion directives. For example, instead of using SOURCE_TABLE_NAME for the REPLACE_TABLES directive, you would need to use "SCHEMA_NAME.SOURCE_TABLE_NAME".

DATA_TYPE

You can use this directive to explicitly map any supported data type between Oracle and PostgreSQL syntax. This directive expects a list of mappings separated by commas. The whole definition must be provided on a single line, but you include multiple DATA_TYPE directives in your configuration file. Use the following format: 

DATA_TYPE ORACLE_DATA_TYPE1:PGSQL_DATA_TYPE1
DATA_TYPE ORACLE_DATA_TYPE2:PGSQL_DATA_TYPE2...

Where ORACLE_DATA_TYPE and PGSQL_DATA_TYPE are data types supported by their respective Oracle and PostgreSQL versions you use in your migration. For information on supported versions, see Scenario overview.

Example:

DATA_TYPE REAL:double precision,SMALLINT:integer

For more information on Oracle and PostgreSQL data types, see:

MODIFY_TYPE

The MODIFY_TYPE directive lets you control to what data type Database Migration Service converts a specific column in your source table. This directive expects a list of mappings separated by commas. The whole definition must be provided on a single line, but you include multiple MODIFY_TYPE directives in your configuration file. Use the following format: 

MODIFY_TYPE SOURCE_TABLE_NAME1:COLUMN_NAME:EXPECTED_END_RESULT_DATA_TYPE
MODIFY_TYPE SOURCE_TABLE_NAME2:COLUMN_NAME:EXPECTED_END_RESULT_DATA_TYPE...

Where:

  • SOURCE_TABLE_NAME is the name of the table that contains the column where you want to change the data type.
  • COLUMN_NAME is the name of the column for which you want to customize the conversion mapping.
  • EXPECTED_END_RESULT_DATA_TYPE is the PostgreSQL data type that you want the converted column to use.

Example:

MODIFY_TYPE events:dates_and_times:DATETIME,users:pseudonym:TEXT

PG_INTEGER_TYPE

By default,Database Migration Service converts the NUMBER(p,s) types to to PostgreSQL DECIMAL(p,s) type.

You can modify this behavior with the PG_INTEGER_TYPE directive. Set its value to 1 and force all your NUMBER with precision and scale (NUMBER(p,s)) types to be converted into PostgreSQL smallint, integer, or bigint types based on the number of precision digits.

Include the following setting in your conversion mapping file: 

PG_INTEGER_TYPE 1

PG_NUMERIC_TYPE

Set this directive to 1 if you want convert all your NUMBER with precision and scale (NUMBER(p,s)) types into PostgreSQL real or float types (based on their number of precision digits).

If you set this directive to 0, your NUMBER(p,s) values preserve their exact original value and use the internal PostgreSQL data type.

Include the following setting in your conversion mapping file: 

PG_NUMERIC_TYPE 1

DEFAULT_NUMERIC

The default conversion for NUMBERs without precision changes whether you also use the PG_INTEGER_TYPE directive:

  • If you use the PG_INTEGER directive, NUMBERs without precision are converted to DECIMAL values.
  • If you don't use the PG_INTEGER directive, NUMBERs without precision are converted to BIGINT values.

You can modify this behavior and use the DEFAULT_NUMERIC directive to specify what data type should be used for NUMBER types without specified precision points. Use the following format: 

DEFAULT_NUMERIC POSTGRESQL_NUMERIC_DATA_TYPE

Where POSTGRESQL_NUMERIC_DATA_TYPE is one of the following: integer, smallint, bigint.

Example:

DEFAULT_NUMERIC integer

REPLACE_COLS

You can use the REPLACE_COLS directive to rename columns in your converted schema. This directive expects a list of mappings separated by commas. Use the following format: 

REPLACE_COLS SOURCE_TABLE_NAME1(SOURCE1_TABLE1_COLUMN_NAME1:DESTINATION_TABLE1_COLUMN_NAME1,SOURCE_TABLE1_COLUMN_NAME2:DESTINATION_TABLE1_COLUMN_NAME2),SOURCE_TABLE_NAME2(SOURCE_TABLE2_COLUMN_NAME1:DESTINATION_TABLE2_COLUMN_NAME1,SOURCE_TABLE2_COLUMN_NAME2:DESTINATION_TABLE2_COLUMN_NAME2)...

Where:

  • SOURCE_TABLE_NAME is the name of the table that contains the column whose name you want to change.
  • SOURCE_COLUMN_NAME is the name of the column in your source whose name you want to change.
  • DESTINATION_COLUMN_NAME is the new name you for the column you want to use in the converted schema.

Example:

REPLACE_COLS events(dates_and_times:event_dates),users(pseudonym:nickname)

REPLACE_TABLES

You can use the REPLACE_TABLES directive to rename tables or move them to a new schema. This directive expects a list of mappings separated by spaces. For more information on the syntax for each use case, expand the following sections.

Renaming tables

To rename tables in your converted schema, use the following format:

REPLACE_TABLES SOURCE_TABLE_NAME1:DESTINATION_TABLE_NAME1 SOURCE_TABLE_NAME2:DESTINATION_TABLE_NAME2

Where:

  • SOURCE_TABLE_NAME is the name of the source table you want to rename in the converted schema.
  • DESTINATION_TABLE_NAME is the new name for the table you want to use in the converted schema.

Example:

REPLACE_TABLES "events:login_events" "users:platform_users"

Moving tables between schemas

You can use this directive to move tables between schemas by adding the schema prefix to the new table name. This mechanism can be used regardless of how you use the SCHEMA directive for the whole conversion file. For example: 

REPLACE_TABLES "events:NEW_SCHEMA_NAME.login_events"

Aliases for customizing data types

When you use conversion directives to modify how Database Migration Service converts different data types (for example, with the DATA_TYPE, MODIFY_TYPE, or PG_NUMERIC_TYPE directives), you can use aliases instead of your source SQL data types.

Expand the following section to see the list of data type aliases supported by Database Migration Service.

Data type aliases

Alias Converted to PostgreSQL type
bigint, int8 BIGINT
bool, boolean BOOLEAN
bytea BYTEA
char, character CHAR
character varying, varchar VARCHAR
date DATE
decimal, numeric DECIMAL
double precision, float8 DOUBLE PRECISION
real, float4 REAL
int, integer, int4 INTEGER
int2 SMALLINT
interval INTERVAL
json JSON
smallint SMALLINT
text TEXT
time TIME
timestamp TIMESTAMP
timestamptz TIMESTAMPTZ
timetz TIMETZ
uuid UUID
XML XML

Sample conversion mapping file

See the following sample conversion mapping file that uses all supported schema conversion directives:

EXPORT_SCHEMA 1
SCHEMA root

PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC integer
DATA_TYPE NUMBER(4\,0):integer
MODIFY_TYPE events:dates_and_times:TIMESTAMP
REPLACE_COLS events(dates_and_times:event_dates),users(pseudonym:nickname)
REPLACE_TABLES events:login_events users:platform_users

The results of using this file are as follows:

  • EXPORT_SCHEMA 1 is a required directive.
  • SCHEMA root results in the following adjustments to the conversion flow:
    • Database Migration Service only performs conversion for entities in the root schema. No other schemas are converted.
    • All other customization directives in this file apply only to columns and data types defined in the root schema.
  • PG_INTEGER_TYPE 1 makes Database Migration Service convert all Oracle numeric data types found in tables in the root schema to PostgreSQL-specific types instead of ANSI portable numeric types.
  • DEFAULT_NUMERIC causes Database Migration Service to convert NUMBER values that don't have a specified precision point into PostgreSQL INTEGER type. This only applies to NUMBER values found in tables in the root schema.
  • DATA_TYPE NUMBER(4\,0):integer causes Database Migration Service to convert specific NUMBER(4,0) values to PostgreSQL INTEGER.
  • MODIFY_TYPE directive causes Database Migration Service to convert the data in the dates_and_times column in the events source table specifically to the PostgreSQL DATETIME type, regardless of the actual source column format.
  • REPLACE_COLS events(dates_and_times:event_dates),users(pseudonym:nickname) makes Database Migration Service rename the following columns in the converted schema:
    • dates_and_times column in the source events table is renamed to event_dates in the same table in converted schema.
    • pseudonym column in the source users table is renamed to nickname in the same table in converted schema.
    The renaming operation is only applied to the events and users tables in the root schema.
  • REPLACE_TABLES events:login_events users:platform_users renames the following tables in the converted schema:
    • The events table is renamed to login_events.
    • The users table is renamed to platform_users.
    The renaming operation is only applied to the events and users tables in the root schema.

Legacy conversion workspaces

Legacy conversion workspaces are an older, more limited type of conversion workspaces. Legacy conversion workspaces don't support Gemini-enhanced conversion features or the interactive SQL editor. You can only use them to convert your source schema with the Ora2Pg migration tool.

We don't recommend using the legacy type of conversion workspaces for your migrations. If your scenario requires the use of legacy conversion workspaces, see Work with legacy conversion workspaces.

What's next