Updated on 2023-11-13 GMT+08:00

Oracle -> GaussDB

Parameter

Feature Name

Description

Affected Object

Suggestion

long_support

LONG data type

It is applicable if tables contain one or more LONG columns. In Oracle, the maximum size of LONG data type is 2 GB. In GaussDB, the maximum size of TEXT data type is 1 GB.

Table, function, stored procedure, trigger, and user-defined type

Oracle LONG supports up to 2 GB data, but there is no equivalent data type in GaussDB. Based on the long_support value you configured, UGO converts LONG to TEXT, whose maximum size is 1 GB in GaussDB. If the column/variable data size is beyond the limit, the data operations (Insert, Update and Variable assignment) will fail and an error will be thrown.

Exceeding the size limit would be a very rare scenario. You can verify the actual data size and understand the maximum data size to be supported. If it is required, you can add one additional column and do appropriate changes wherever the column is referred.

longraw_support

LONG RAW data type

It is applicable if tables contain one or more LONG RAW columns. In Oracle, the maximum size of LONG RAW data type is 2 GB. In GaussDB, BYTEA data type supports a maximum of 53862709 bytes.

Table, function, stored procedure, trigger, and user-defined type

Oracle LONG RAW supports up to 2 GB data; but there is no equivalent data type in GaussDB. Based on the long_support value you configured, UGO converts LONG RAW to BYTEA whose maximum size is 536862709 bytes (512 MB-8203 bytes). If the column/variable data size is beyond the limit, the data operations (Insert, Update and Variable assignment) will fail and an error will be thrown.

Exceeding the size limit would be a very rare scenario. You can verify the actual data size and understand the maximum data size to be supported. If it is required, you can add one additional column and do appropriate changes wherever the column is referred.

clob_datatype_show_error

CLOB data type

It is applicable if tables contain one or more CLOB columns. In Oracle, the maximum size of CLOB data type is 4 GB. In GaussDB, the maximum size of CLOB/TEXT data type is 1 GB.

Table, function, stored procedure, trigger, and user-defined type

Oracle CLOB supports a maximum of 4 GB data. Even CLOB is supported by GaussDB, but it supports a maximum of 1 GB data. Based on the clb_datatype_show_error value you configured, UGO logs an error. If the column/variable data size is beyond the limit, the data operations (Insert, Update and Variable assignment) will fail and an error will be thrown.

Exceeding the size limit would be a very rare scenario. You can verify the actual data size and understand the maximum data size to be supported. If it is required, you can add one additional column and do appropriate changes wherever the column is referred.

nclob_support

NCLOB data type

It is applicable if tables contain one or more NCLOB columns. In Oracle, the maximum size of NCLOB data type is 4 GB. In GaussDB, the maximum size of TEXT data type is 1 GB.

Table, function, stored procedure, trigger, and user-defined type

Oracle NCLOB supports a maximum of 4 GB data, but there is no equivalent data type in GaussDB. Based on the nclob_support value you configured, UGO converts NCLOB to TEXT whose maximum size is 1 GB in GaussDB. If the column/variable data size is beyond the limit, the data operations (Insert, Update and Variable assignment) will fail and an error will be thrown.

Exceeding the size limit would be a very rare scenario. You can verify the actual data size and understand the maximum data size to be supported. If it is required, you can add one additional column and do appropriate changes wherever the column is referred.

float_support

FLOAT data type

It is applicable if tables contain one or more FLOAT columns and FLOAT is specified as FLOAT/FLOAT(n), where n > 53. If n is not provided, the default value 126 is used.

Table, function, stored procedure, trigger, and user-defined type

In Oracle, the default (maximum) precision of FLOAT is 126. GaussDB supports FLOAT, which is implicitly converted as DOUBLE PRECISION. Based on the float_support value you configured, UGO converts FLOAT to DOUBLE PRECISION (whose maximum precision is 53) or NUMBER (up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point). If it is converted to DOUBLE PRECISION, the precision specified above 53 will throw an error and the statement will fail.

You can configure the parameter based on the actual precision value. If the precision is not specified or it is greater than 53, you can set the parameter to NUMBER or FLOAT(53).

xml_support

XMLTYPE data type

It is applicable if tables contain one or more XMLTYPE columns.

Table, function, stored procedure, trigger, and user-defined type

Oracle XMLTYPE supports a maximum of 4 GB data, but there is no equivalent data type in GaussDB. Based on the xml_support value you configured, UGO converts XMLTYPE to TEXT or JSON. Both the types support the maximum size of 1 GB in GaussDB and do not match with Oracle XMLTYPE.

Exceeding the size limit would be a very rare scenario. You can verify the actual data size and understand the maximum data size to be supported. If it is required, you can add one additional column and do appropriate changes wherever the column is referred. It can be handled by writing the user-defined functions or writing application-side code.

number_with_zero_scale

NUMBER data type with zero scale

It is applicable if tables contain NUMBER with zero scale or NUMBER without scale.

Table, function, stored procedure, trigger, and user-defined type

For NUMBER with zero scale, UGO converts NUMBER to INTEGER (smallint, int, bigint).

SDO_GEOMETRY

SDO_GEOMETRY object type

It is applicable if tables contain SDO_GEOMETRY object type. There is no equivalent for SDO_GEOMETRY in GaussDB.

Table, function, stored procedure, trigger, and user-defined type

There is no equivalent for SDO_GEOMETRY in GaussDB. Since GaussDB supports user-defined data type, SDO_GEOMETRY can be converted into an appropriate user-defined data type and do the appropriate change wherever a column/variable with this type is referred.

MBRCOORDLIST

MDSYS.MBRCOORDLIST object type

It is applicable if table scripts contain MDSYS.MBRCOORDLIST object type. There is no equivalent for MDSYS.MBRCOORDLIST in GaussDB.

Table, function, stored procedure, trigger, and user-defined type

There is no equivalent for MDSYS.MBRCOORDLIST in GaussDB. Since GaussDB supports user-defined data type, MDSYS.MBRCOORDLIST can be converted into an appropriate user-defined data type and do the appropriate change wherever a column/variable with this type is referred.

GEOMETRY

GEOMETRY object type

It is applicable if the table script contains GEOMETRY object type. There is no equivalent for GEOMETRY in GaussDB.

Table, function, stored procedure, trigger, and user-defined type

There is no equivalent for GEOMETRY in GaussDB. Since GaussDB supports user-defined data type, GEOMETRY can be converted into an appropriate user-defined data type and do the appropriate change wherever a column/variable with this type is referred.

partition_columns_with_unique_constraint

Table column partition with unique constraint

It is applicable if table scripts contain the partition syntax and primary keys or unique keys.

Table, index

There is a limitation in GaussDB that the columns specified in unique keys (including primary keys) should be a super set of partition key columns. Because of this limitation, tables will fail to be created if unique constraints do not contain any one of the partition key columns.

The unique constraints will be commented or the partition columns will be added to the unique constraints based on the partition_columns_with_unique_constraint value. Because of this conversion, there is a possibility of creating duplicate records in tables and so it will also raise issues in creating referential integrity constraints in child tables. It is not supported by GaussDB.

mig_interval_partition

Interval partition in tables

It is applicable if table scripts contain interval partition syntax.

Table

Since GaussDB does not support interval partition, this syntax can be commented during the conversion based on the mig_interval_partition value you configured. After the partition syntax is commented, the table will become a normal table (not a partitioned table) and you will miss out the partition benefits such as performance, manageability and availability.

It can be handled manually by creating multiple tables (on different tablespaces) based on the interval partition conditions specified and do the appropriate changes wherever a table with partition syntax is referred.

on_null_support

DEFAULT ON NULL clauses

It is applicable if the input table scripts contain DEFAULT ON NULL keyword. A DEFAULT ON NULL clause will configure DEFAULT for a column even when a null value has been explicitly included in the INSERT statement. There is no equivalent for DEFAULT ON NULL in GaussDB.

Table

There is no equivalent for DEFAULT ON NULL in GaussDB. It can be handled by writing an INSERT trigger on a table.

generated_identity_support

IDENTITY columns

It is applicable if input table scripts contain IDENTITY columns using GENERATED AS IDENTITY.

Table, sequence

You can set this parameter to an appropriate value (such as, Serial, Small Serial, BIG Serial, or Default sequence).

read_only_table

Read only clause in tables

It is applicable if the input table scripts contain read only keyword. In Oracle, tables are marked as read only using the ALTER TABLE command. When a table is in read-only mode, operations that attempt to modify table data are not allowed.

Table

You can set this parameter to a proper value, for example, Comment, Ignore, or Trigger.

global_temp_table_support

Global temporary tables

It is applicable if the input table scripts contain GLOBAL TEMPORARY keyword. A global temporary table is a permanent database object. Its table structure will be retained in the database even after the session is disconnected. A local temporary table is scoped to the session in which you created it. Its table is deleted once the session disconnects. GaussDB supports only local temporary tables.

Table

GaussDB distributed version does not support global temporary tables. So global temporary tables will be converted to local temporary tables based on the global_temp_table_support value. When a session ends, data in the local temporary tables is deleted, and the tables are deleted. However, only the data in global temporary tables is deleted.

You call one use-defined function to check whether a temporary table exists in the places wherever the table is referred and create a table if not exists.

comment_foreign_key

FOREIGN KEY constraints

It is applicable if the input scripts contain foreign keys.

Table, index

The distributed GaussDB does not support foreign keys. Foreign keys will be commented based on the comment_foreign_key value you configured. Comment foreign keys does not ensure that data remains consistent.

This can be handled manually by writing applicable DML (Insert, Update, Delete) triggers on the source tables.

unused_column_support

UNUSED keyword

It is applicable if the input table scripts contain UNUSED keyword.

Table

You can set this parameter to a proper value. The value 0 indicates that no conversion is performed, and the value 1 indicates that unused columns are deleted.

bitmap_index

BITMAP index

It is applicable if CREATE INDEX statements contains BITMAP keyword.

Index

You can set this parameter to a proper value. The value 0 indicates that BITMAP index is not converted. The value 1 indicates that BITMAP index is commented. The value 2 indicates that BTREE index is created.

reverse_support

REVERSE keyword in CREATE INDEX statements

It is applicable if CREATE INDEX statements contain REVERSE keyword.

Index

You can set this parameter to a proper value. IGNORE indicates that REVERSE is not converted. COMMENT indicates that REVERSE is commented.

unsupported_func_index

Functions in indexes

It is applicable if CREATE INDEX statements contain unsupported functions like TRUNC function.

Index

You can set this parameter to a proper value. The value 0 indicates that no conversion is performed. The value 1 indicates that the CREATE INDEX statement is commented.

comment_tablespace

Tablespace (Tablespace mapping has higher priority than this configuration)

It is applicable if CREATE TABLE and CREATE INDEX statements contain TABLESPACE keyword.

Tablespace, table

You can set this parameter to a proper value. The value FALSE indicates that TABLESPACE is not converted, and the value TRUE indicates that TABLESPACE is commented.

viewEditioning

EDITIONING views

It is applicable if the input view scripts contain EDITIONING keyword. In Oracle, it is allowed to create DML triggers on EDITIONING views.

View

You can set this parameter to a proper value. The value 0 indicates that EDITIONING is not converted. The value 1 indicates that EDITIONING is commented.

seq_max_min_value

Sequence value beyond the limit

It is applicable if sequence scripts contain MAXVALUE or MINVALUE, which is beyond the limits supported by GaussDB.

Sequence, table

Oracle supports MAXVALUE and MINVALUE up to 1027 and -1026 respectively. If the input MINVALUE or MAXVALUE of a sequence goes beyond the limits (-9223372036854775808 to 9223372036854775807), MINVALUE or MAXVALUE will be replaced with the boundary value for distributed and primary/standby GaussDB 1.4 Enterprise Edition and the LARGE SEQUENCE value for primary/standby GaussDB 2.0 Enterprise Edition or later. The generated sequence value will not exceed the sequence range allowed for GaussDB. If MINVALUE or MAXVALUE is beyond the limit, compiling the scripts on GaussDB will fail.

Exceeding the size limit would be a very rare scenario. It can be handled by introducing a table which holds sequence details including the last sequence value, ascending/descending, increment value. Besides, the user-defined functions can be provided to match with NEXTVAL and CURRVAL functions in Oracle.

otherthan_gregorian

Calendar types other than GREGORIAN

It is applicable if the input TO_DATE function contains calendar type other than GREGORIAN.

Table

GaussDB supports only GREGORIAN calendar for TO_DATE and TO_TIMESTAMP. If other than GREGORIAN calendar is specified as third parameter in Oracle TO_DATE, it will be commented based on otherthan_gregorian you configured.

Alternate solution is not available.

default_schema

Setting default schema names

It is applicable if schema names are not provided in creating database objects.

schema

You need to set schema names to default names as required.

package_name_delimiter

Setting package name delimiters

It is applicable if the input scripts contain CREATE PACKAGE statements.

Function, trigger, and stored procedure

GaussDB supports packages in some versions. You can associate object names with the subobjects.

pkg_naming

Packages

It is applicable for packages. Based on parameter configuration, you can use packages as schemas or merge package names along with procedure and function names.

Package

GaussDB supports packages in some versions. You can associate object names with the sub objects.

varray_size

PL/SQL tables

It is applicable if PL/SQL tables are used in stored procedures, functions and packages.

Package, function, and stored procedure

You can set the VARRAY size as required.

record_type_usage_handling

RECORD type

It is applicable when RECORD type is used in stored procedures, functions and packages.

Package, function, and stored procedure

-

deployment_type

Deployment mode

It is applicable for features supported by a deployment mode.

Package, function, and stored procedure

There is a limitation in GaussDB that it supports only a list of data types for distribution key columns. If a table does not contain any of the columns with these data types, this table will fail to be created. Besides, if the data type of the first column (as primary key or unique constraint) is not any of the supported data types, this table will fail to be created.

It is rare that all data types in a table are not supported. Based on configuration, the data type of the identified distribution column can be converted an equivalent and supported data type. For example, if the identified distribution column is defined with DOUBLE PRECISION data type (which is an unsupported data type for the distribution column), it can be converted to NUMBER.

clob_function_support

CLOB in functions

It is applicable if functions contain one or more CLOB columns. In Oracle, the maximum size of CLOB data type is 4 GB. In GaussDB, the maximum size of CLOB/TEXT data type is 1 GB.

Function, stored procedure, trigger, and user-defined type

You can set this parameter to a proper value. IGNORE indicates no conversion is performed, and MIGRATE indicates CLOB data type is converted to a proper data type.

blob_datatype_show_error

BLOB data type

It is applicable if functions contain one or more BLOB columns. In Oracle, the maximum size of BLOB data type is 4 GB. In GaussDB, the maximum size of BLOB data type is 530 MB.

Table, function, stored procedure, trigger, and user-defined type

Oracle BLOB supports a maximum of 4 GB data. Even BLOB data type is supported by GaussDB, but it supports a maximum of 536862709 bytes (512 MB–8203 bytes). Based on the blob_datatype_show_error value you configured, UGO logs an error. If the column/variable data size is beyond the limit, the data operations (Insert, Update and Variable assignment) will fail and an error will be thrown. Exceeding the size limit would be a very rare scenario. You can verify the actual data size and understand the maximum data size to be supported. If it is required, you can add one additional column and do appropriate changes wherever the column is referred.

temptable_schemaname_support

Conversion of global temporary table names

It is applicable if input table scripts contain GLOBAL TEMPORARY keyword and the instance is a distributed instance. A global temporary table will be converted to a local temporary table. In distributed mode, GaussDB does not support schema names for temporary tables.

Table

-

unique_in_distributed_env

Unique constraints and indexes in a distributed database with multiple data nodes

It is applicable if target databases with multiple data nodes contain unique constraints or indexes.

Table, index

There is a limitation in GaussDB that the column list specified in primary key constraints or unique constraints or indexes should be a super set of distribution key column list. Due to this limitation, CREATE TABLE, ALTER TABLE ADD CONSENT and CREATE UNIQUE INDEX statements can fail in the distributed environment. Based on the unique_in_distributed_env value you configured, unique constraints/indexes will be commented, unique indexes will be converted to normal indexes (by commenting UNIQUE keyword), or distribution columns will be added to unique constraints/indexes. Because of this conversion, there is a possibility of creating duplicate records in tables and so it will also raise issues in creating referential integrity constraints in child tables.

migrate_distributed_table

Migrating distributed tables and indexes

Tables and indexes in the distributed environment are not migrated.

Table, index

-

external_global_user_support

Global or external users

Based on configuration, you can ignore or comment GLOBAL or EXTERNAL USER statements or convert external/global users to normal database users.

System

-

system_role_support

System-defined roles

System roles cannot be granted to users. This configuration will handle such type of requirements.

System

-

gauss_version

GaussDB versions

Based on the configuration, UGO will migrate packages.

Package

-

list_partition_support

LIST partition

Distributed GaussDB 3.2 and primary/standby GaussDB support the LIST partition. This configuration will handle such type of requirements.

Table, index

Distributed GaussDB 3.1 and earlier versions do not support the LIST partition. You can comment the LIST partition syntax during the conversion. After the partition syntax is commented, the table will become a normal table (not a partitioned table) and you will miss out the partition benefits such as performance, manageability and availability.

It can be handled manually by creating multiple tables (on different tablespaces) based on the LIST partition conditions specified and do the appropriate changes wherever a table with partition syntax is referred.

reserved_keyword_objectname_support

Case format of object names (as reserved keywords)

If object names contain reserved keywords, they will be converted to UPPERCASE or LOWERCASE based on this configuration.

Table, index, package, materialized view, function, stored procedure, trigger, user-defined type, view

-

udt_support

User-defined types

Some GaussDBV5R1 versions do not support user-defined types. This configuration will handle such type of requirements.

Package, function, and stored procedure

-

hierarchial_clause_support

Hierarchical query

Some GaussDB versions do not support hierarchical query. This configuration will handle such type of requirements.

Package, materialized view, function, stored procedure, trigger, and view

-

bulk_collect_support

BULK COLLECT syntax

GaussDB V5R2 and later versions support the BULK COLLECT syntax. Based on this configuration, you can convert the BULK COLLECT syntax.

Package, function, stored procedure, and trigger

-

savepoint_support

SAVEPOINT syntax

GaussDB V5R2 and later versions support the SAVEPOINT syntax. This configuration will handle such type of requirements.

Package, function, stored procedure, and trigger

-

autonomous_transaction_support

AUTONOMOUS TRANSACTION syntax

GaussDB V5R2 does not support the AUTONOMOUS TRANSACTION syntax. This configuration will handle such type of requirements.

Package, stored procedure, function, and trigger

-

large_seq_cycle

CYCLE IN SEQUENCE syntax

GaussDB V5R1 does not support the CYCLE IN SEQUENCE syntax. This configuration will handle such type of requirements.

Sequence

-

udt_support_inside_package

User-defined type inside packages

GaussDB does not support the user-defined type. This configuration will handle such type of requirements.

Package, function, and stored procedure

-

insert_record_variable_support

RECORD VARIABLE in INSERT statements

GaussDB V5R2 does not support RECORD VARIABLE in INSERT statements. This configuration will handle such type of requirements.

Package, stored procedure, and function

-

forall_support

FOR ALL

GaussDB V5R2 does not support the FOR ALLSEQUENCE syntax. This configuration will handle such type of requirements.

Package, stored procedure, and function

-

rownum_support

ROWNUM

Distributed GaussDB 3.1 and earlier versions do not support ROWNUM. This configuration will handle such type of requirements.

Package, stored procedure, and function

-

pkg_cursor_variable_support

Cursor variables

GaussDB V5R2 does not support cursor variables defined in package specifications. This configuration will handle such type of requirements.

Package

-

subpartition_support

Sub partition

Some GaussDB V5R2 versions do not support the sub partition. This configuration will handle such type of requirements.

Table

-

subpartition_index_support

Index sub partition

Some GaussDB V5R2 versions do not support the index sub partition. This configuration will handle such type of requirements.

Table, index

-

interval_partition_support

Interval partition

Some GaussDB V5R2 versions do not support the interval partition. This configuration will handle such type of requirements.

Table

-

hash_partition_support

Hash partition

Some GaussDB V5R2 versions do not support the hash partition. This configuration will handle such type of requirements.

Table

-

alter_synonym_owner

Synonyms

Some GaussDB V5R2 versions do not support synonyms. This configuration will handle such type of requirements.

Synonyms

-

any_index_privilege_support

Index privileges

GaussDB V5R2 provides different syntaxes for index privileges. This configuration will handle such type of requirements.

Index

-

any_trigger_privilege_support

Trigger privileges

GaussDB V5R2 provides different syntaxes for trigger privileges. This configuration will handle such type of requirements.

Trigger

-

create_any_object_privilege_support

Object privileges

GaussDB V5R2 provides different syntaxes for object privileges. This configuration will handle such type of requirements.

All objects

-

use_user_connection

User connection

It is applicable for users who need to execute objects on GaussDB V5R2

All objects

-

sqlplus_unsupported_command

SQL PLUS

GaussDB V5R2 does not support SQL PLUS commands. This configuration will handle such type of requirements.

SQL PLUS commands

SQL PLUS tool used in Oracle has a set of commands which are not supported in GaussDB. This will have impact on file creation and alteration and the final results. SQL PLUS is used to avoid errors during UGO migration.

GaussDB does not support SQL PLUS commands. You need to re-write the scripts using the GaussDB commands.

unsupported_set_attributes_support

DBMS_SCHEDULER.SET_ATTRIBUTE

GaussDB V5R2 does not fully support DBE_SCHEDULER.SET_ATTRIBUTE which is equivalent to DBMS_SCHEDULER.SET_ATTRIBUTE in Oracle. This configuration will handle such type of requirements.

Stored procedure, function, trigger, package, job, schedule, program, credential, and job class

-

supported_set_attributes

DBMS_SCHEDULER.SET_ATTRIBUTE

This configuration will contain all the set-attributes supported by GaussDB to migrate Oracle DBMS_SCHEDULER.SET_ATTRIBUTE.

Stored procedure, function, trigger, package, job, schedule, program, credential, and job class

-

create_db_destination_support

DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION

There is no equivalent for DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION in GaussDB V5R2. This configuration will handle such type of requirements.

Stored procedure, function, trigger, package, job, schedule, program, credential, and job class

-

authid_support

AUTHID clause of PL/SQL objects

If AUTHID DEFINER/INVOKER is not specified, you can determine whether AUTHID DEFINER is added or not based on the parameter configuration. In GaussDB V5R2C00 and later versions, the default value is AUTHID DEFINER, which matches the Oracle configuration. In GaussDB V5R1, the default value is AUTHID INVOKER.

Stored procedure, function, package, type

-

systemview_case_support

Object name case

GaussDB V5R2 stores database objects in lowercase, while Oracle stores objects in uppercase. This configuration will handle such type of requirements.

System view

-

special_character_objectname_support

Case format of object names containing special characters

The object names which include special characters in the target DB can be converted to UPPERCASE and LOWERCASE based on the parameter configuration.

Table, index, package, materialized view, function, stored procedure, trigger, user-defined type, view

-

create_object_privilege_support

Object-level privileges

GaussDB V5R2 does not support object-level privileges. This configuration will handle such type of requirements.

System

-

package_name_same_schema

Package name

It is applicable if packages and schemas have the same names.

Package

-

system_privilege_support

System privileges

GaussDB V5R2 does not support system privileges. This configuration will handle such type of requirements.

System

-

mismatch_charset_support

Mismatch character set

The source character set does not match the target character set. This configuration will handle such type of requirements.

All objects

-

src_character_set

Source character set

It is used to select the character set of the source database.

All objects

-

target_character_set

Target database character set

It is used to select the character set of the target database.

All objects

-