Console
    Start free

    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:

    There are certain data types that are unsupported for Oracle migrations. For more information, see Known limitations for data types .

    Conversion progress overviews

    Conversion workspaces robust overview information, where you can gain insights into the total number of outstanding or solved conversion issues, Gemini-assisted augmentations, and the general health of your conversion process.

    Conversion workspace screen with the Conversion Overview tab where you can see the number of converted objects, conversion issues, and Gemini-assisted conversion enhancements.
    Figure 1. Conversion workspace overview screen, where you can monitor the progress of your conversion, view issues and inspect the resulting PostgreSQL code. (click to enlarge)
    Conversion workspace screen with the Conversion Overview tab where you can see the number of converted objects, conversion issues, and Gemini-assisted conversion enhancements.

    You can use this view to filter objects in your schema by type, issue severity, actions needed, or conversion status.

    Conversion workspace screen that shows how you can filter converted objects by type or status.
    Figure 2. Converted objects filtering by status and object type. (click to enlarge)
    Conversion workspace screen that shows how you can filter converted objects by type or status.

    For more information about using conversion overviews to inspect conversion results, see Work with conversion workspaces .

    Deterministic code and schema conversion

    When you create a conversion workspace , Database Migration Service immediately performs the initial schema conversion using a set of deterministic conversion rules where specific Oracle data types and objects are mapped to specific PostgreSQL data types and objects. This process supports a very specific subset of available Oracle database objects.

    Deterministic 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

    Accelerate code and schema conversion with Gemini

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

    • Enhance the deterministic conversion results with Gemini-powered auto-conversion to use the power of AI to significantly reduce the number of manual adjustments needed in your PostgreSQL code.

    • 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-powered 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 causes all other customization directives provided in your file to apply to objects in this particular schema.

    • When you use this directive, other schemas contained in your database are also converted, but their objects aren't subject to any modifications.
    • If you include this directive in the conversion mapping file, all customizations are applied only to objects contained in this specific schema.
    • If you skip this directive, you must provide fully-qualified object names that include the schema name 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 " .
    • To customize objects in different schemas, try the following:
      • Create separate conversion mapping files for other schemas, and upload them to the conversion workspace.
      • Use fully-qualified object names that include the schema name for objects that reside in different schemas than the one you provide to the SCHEMA directive.

    Use the following format:

    SCHEMA SCHEMA_NAME

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

    CASE_HANDLING

    By default, Database Migration Service converts all object names to lowercase. You can use the CASE_HANDLING directive to modify this behavior.

    • This directive is unaffected by the SCHEMA directive. It works globally, and affects all objects in the conversion workspace.
    • The RENAME_* , MOVE_* , and REPLACE_* directives take precedence over the CASE_HANDLING directive and rename your objects exactly, regardless of the CASE_HANDLING property.
    • If this directive exists in multiple configuration files with conflicting values, Database Migration Service raises an error during schema import.

    Use the following format:

    CASE_HANDLING OPTION

    Where OPTION can be one of the following:

    • UPPERCASE : Converts all object names to uppercase.
    • LOWERCASE : Converts all object names to lowercase (default behavior).
    • PRESERVE_ORIGINAL : Keeps the original casing from the source schema. This is useful if your applications use case-sensitive identifiers.

    Example :

    CASE_HANDLING PRESERVE_ORIGINAL

    Renaming Objects ( RENAME_* )

    You can rename different database objects during conversion. Database Migration Service automatically updates all code references (in views, stored procedures, functions, etc.) to use the new names.

    General syntax 

    RENAME_ OBJECT_TYPE 
 SOURCE_NAME1 
: DESTINATION_NAME1 
 SOURCE_NAME2 
: DESTINATION_NAME2 
...

    Important considerations

    • The RENAME_* directives are case-sensitive for the destination object name and take precedence over the CASE_HANDLING directive. For example, if you use both directives:

      SCHEMA MySchema
CASE_HANDLING PRESERVE_ORIGINAL
# Destination objects are renamed exactly
# to 'SoMe_tAbLe' and 'RenamedView', respecting the case
# despite the CASE_HANDLING directive
RENAME_TABLES some_table:SoMe_tAbLe
RENAME_VIEWS MyView:RenamedView

    • For the SOURCE_NAME , always refer to the original object name, even if you use other directives such as MOVE_* . For example, if you want to rename one of your view objects and move it to a new schema, refer to the original view name for both directives:

      RENAME_VIEWS MyView:MyRenamedView
MOVE_VIEWS MyView:MyOtherSchema
    • The RENAME_TABLES directive overrides the REPLACE_TABLES directive in a single file. If you want to both rename and move a table, we recommend you use the MOVE_* directive instead.

    • The full format of the SOURCE_NAME variable depends on whether you also use the SCHEMA directive:

      • With SCHEMA directive: Use unqualified names, for example MyTable .
      • Without SCHEMA directive: Use fully qualified names, for example MySchema.MyTable .

    Supported RENAME_* directives

    • RENAME_SCHEMA : Renames a schema.
      A single configuration file can contain only one RENAME_SCHEMA directive. If the SCHEMA directive is provided, RENAME_SCHEMA can rename only that particular schema.
    • RENAME_TABLES : Renames tables. Overrides the REPLACE_TABLES in the same file.
    • RENAME_COLUMNS : Renames columns within tables. Overrides the REPLACE_COLS directive in the same file. Use the following format: 
      RENAME_COLUMNS TABLE1 
. SRC_COL 
: DEST_COL 
 TABLE2 
. SRC_COL 
: DEST_COL

      If you use the SCHEMA directive, use unqualified table names. If you don't use the SCHEMA directive, include the fully qualified table names, such as SCHEMA.TABLE1 .

    • RENAME_VIEWS
    • RENAME_MATERIALIZED_VIEWS
    • RENAME_SEQUENCES
    • RENAME_FUNCTIONS
    • RENAME_STORED_PROCEDURES
    • RENAME_TRIGGERS

    • RENAME_PACKAGES : Database Migration Service converts Oracle packages to PostgreSQL schemas. If your schema contains packages that share names, the PostgreSQL code might encounter name conflicts when it tries to create two schemas with the same name. You can use this directive to avoid such conflicts.

      For example, if you have packages like SALES.REPORTING_PKG and HR.REPORTING_PKG , you can rename them to distinct names:

      RENAME_PACKAGES SALES.UTILS:SALES_UTILS
RENAME_PACKAGES HR.UTILS:HR_UTILS

    • RENAME_USER_DEFINED_TYPES

      Available alias: RENAME_UDTS .

    Moving Objects ( MOVE_* )

    You can move objects to different schemas in the destination database. This is useful for reorganizing your database structure during migration. Database Migration Service automatically updates all code references in views, stored procedures, functions, etc.

    General syntax 

    MOVE_ OBJECT_TYPE 
 SOURCE_NAME1 
: DESTINATION_SCHEMA1 
 SOURCE_NAME2 
: DESTINATION_SCHEMA2 
...

    Important considerations

    • For the SOURCE_NAME , always refer to the original object name, even if you use other directives such as RENAME_* . For example, if you want to rename one of your view objects and move it to a new schema, refer to the original view name for both directives:

      RENAME_VIEWS MyView:MyRenamedView
MOVE_VIEWS MyView:MyOtherSchema
    • The directive expects only the DESTINATION_SCHEMA name, not the full object name.

    • The full format of the SOURCE_NAME variable depends on whether you also use the SCHEMA directive:

      • With SCHEMA directive: Use unqualified names, for example MyTable .
      • Without SCHEMA directive: Use fully qualified names, for example MySchema.MyTable .

    Supported MOVE_* directives

    • MOVE_TABLES : Moves tables to a different schema. Takes precedence over REPLACE_TABLES for schema changes in a single configuration file.
    • MOVE_VIEWS
    • MOVE_MATERIALIZED_VIEWS
    • MOVE_SEQUENCES
    • MOVE_FUNCTIONS
    • MOVE_STORED_PROCEDURES

    • MOVE_USER_DEFINED_TYPES

      Available alias: MOVE_UDTS .

    Example: Reorganizing schemas 

    SCHEMA LegacyApp

# Moves the 'LegacyApp.Users' and 'LegacyApp.Orders' tables
# to the 'data' schema.
MOVE_TABLES Users:data Orders:data

# Moves the 'LegacyApp.CreateUser' and 'LegacyApp.ProcessOrder'
# stored procedures to the 'api' schema
MOVE_STORED_PROCEDURES CreateUser:api ProcessOrder:api

# Moves the 'LegacyApp.SalesSummary' views to the 'reporting' schema
MOVE_VIEWS SalesSummary:reporting

    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 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 NUMBER s without precision changes whether you also use the PG_INTEGER_TYPE directive :

    • If you use the PG_INTEGER directive, NUMBER s without precision are converted to DECIMAL values.
    • If you don't use the PG_INTEGER directive, NUMBER s 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. If you don't use the SCHEMA directive, make sure you use the fully-qualified table name: SCHEMA_NAME . SOURCE_TABLE_NAME
    • 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.

    If you don't use the SCHEMA directive, make sure you use the fully qualified table names in quotes for both source and destination variables:

    • " SCHEMA_NAME . SOURCE_TABLE_NAME "
    • " SCHEMA_NAME . DESTINATION_TABLE_NAME "

    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 some of the supported schema conversion directives:

    EXPORT_SCHEMA  
 1 
SCHEMA  
root # Preserve original casing for all objects 
CASE_HANDLING  
PRESERVE_ORIGINAL # Data type conversions 
PG_NUMERIC_TYPE  
 0 
PG_INTEGER_TYPE  
 1 
DEFAULT_NUMERIC  
integer
DATA_TYPE  
NUMBER ( 
 4 
 \, 
 0 
 ) 
:integer
MODIFY_TYPE  
events:dates_and_times:TIMESTAMP # Renaming objects using the RENAME_* directives 
 # These allow case-sensitive destination names 
RENAME_TABLES  
events:LoginEvents  
users:PlatformUsers
RENAME_COLUMNS  
events.dates_and_times:EventDates  
users.pseudonym:Nickname
RENAME_VIEWS  
InternalReport:FinInternalReport # Moving objects to new schemas using the MOVE_* directives 
MOVE_TABLES  
audit_log:archive
MOVE_VIEWS  
InternalReport:reporting

    The results of using this file are as follows:

    • EXPORT_SCHEMA 1 is a required directive.
    • SCHEMA root causes the other directives to apply to objects within the root schema, unless fully qualified names are used.
    • CASE_HANDLING PRESERVE_ORIGINAL ensures that all object names from the source root schema retain their original casing in the destination (unless overridden by a RENAME_* directive).
    • 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 integer causes Database Migration Service to convert NUMBER values that don't have a specified precision point into PostgreSQL INTEGER type.
    • DATA_TYPE NUMBER(4\,0):integer causes Database Migration Service to convert specific NUMBER(4,0) values to PostgreSQL INTEGER .
    • MODIFY_TYPE events:dates_and_times:TIMESTAMP directive causes Database Migration Service to convert the data in the dates_and_times column in the events source table specifically to the PostgreSQL TIMESTAMP type.
    • RENAME_TABLES events:LoginEvents users:PlatformUsers renames tables, preserving the specified case:
      • The events table is renamed to LoginEvents .
      • The users table is renamed to PlatformUsers .
    • RENAME_COLUMNS events.dates_and_times:EventDates user.pseudonym:Nickname renames columns, preserving the specified case in the destination:
      • In the LoginEvents table (original name events ), column dates_and_times is renamed to EventDates .
      • In the PlatformUsers (original name users ), column pseudonym is renamed to Nickname .
    • RENAME_VIEWS InternalReport:FinInternalReport renames the view InternalReport to FinInternalReport .
    • MOVE_TABLES audit_log:archive moves the audit_log table from the root schema to the archive schema.
    • MOVE_VIEWS InternalReport:reporting moves the InternalReport view to the reporting schema. This view is also renamed to FinInternalReport because of the RENAME_VIEWS directive. Database Migration Service handles the dependency: the object is first renamed, then moved.

    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

    To learn about using conversion workspaces, see:
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: