Editing the Configurations During Migration from Oracle to GaussDB

When table structure of all Oracle features is collected, the verification success rate is low. All features cannot be covered due to a huge difference of the source and target databases.

Table structure conversion mode

Collect only the table names, column names, column constraints, index, and primary keys of tables.

Impacts:

1. Table names, column names, column constraints, unique indexes, and primary keys are retained, but foreign keys, tablespaces, and physical storage are ignored.
2. LIST and RANGE partitions are remained, but HASH, INTERVA, automatic, level-2, and virtual column partitions are ignored.
3. If the conversion configuration items related to the feature are ignored, the processing will also be ignored.

During object creation, the field values are in uppercase by default in Oracle, but are in lowercase in GaussDB. However, the field values in the quotation marks remain unchanged.

Case conversion for object names

Convert object names to lowercase letters.

If an object name is reserved keywords in GaussDB, the case of the object name will need to be converted.

Case forma of reserved keywords in object names

If the object name contains reserved keywords written in uppercase, convert the keywords to lowercase. If the object name contains reserved keywords written in uppercase and lowercase, do not make any changes.

In Oracle, RECORD types can be generated inside stored procedures, but GaussDB does not support record type generation inside stored procedures.

Record types

Create and use RECORD types for stored procedures and functions.

GaussDB does not support the Oracle character set.

Character set for the target database

Select UTF8 as the character set for the target database.

The size of the GaussDB V5R2 character set is different from that of the Oracle character set.

Mismatch character set

Convert CHAR(n), VARCHAR(n), and VARCHAR2(n) to the national character set.

The distributed GaussDB V5R2 does not support Oracle packages.

Package names

Merge the package names along with the stored procedure names.

GaussDB does not support Oracle FOREIGN KEY constraints.

FOREIGN KEY constraints

Comment FOREIGN KEY constraints.

GaussDB does not support CHARACTER_SET.

CHARACTER_SET

Comment CHARACTER_SET.

GaussDB V5R1 does not support Oracle CYCLE IN SEQUENCE syntax.

CYCLE IN SEQUENCE

Comment CYCLE IN SEQUENCE in GaussDB V5R2 and earlier versions. The migrated script is compiled and no error is reported.

By default, the object names in system views created by the Oracle database are in uppercase, and object names in system views created by the GaussDB are in lowercase. If the object names with quotation marks are forcibly in uppercase, they cannot be referenced.

Object name case in system views

Convert object names to lowercase letters.

GaussDB V5R2 does not support CREATE privileges for objects such as tables, types, triggers, synonyms, sequences, and procedures. If the grants are not provided to the respecting objects, the object creation will fail.

Object-level privileges

Grant schema-level privileges.

GaussDB does not support the Oracle character set.

Character set for the source database

Select AL32UTF8 as the character set of the source database.

GaussDB supports TABLESPACE causes in CREATE TABLE and CREATE INDEX statements. However, if the specified tablespace does not exist in GaussDB, the CREATE TABLE and CREATE INDEX statements will fail to be executed. The default tablespace is recommended.

TABLESPACE (Tablespace mapping has higher priority than this configuration.)

Comment TABLESPACE clauses.

Object cannot be executed in the target database due to insufficient privileges.

User connections

Set sysadmin to the user having the privilege to create and execute the GaussDB script.

GaussDB V5R1 does not support FOR ALL syntax used in stored procedures.

FOR ALL syntax

Migrate the script. FOR ALL is converted to FOR LOOP.

Oracle supports MAXVALUE and MINVALUE up to 10²⁷ and -10²⁶ respectively. MINVALUE/MAXVALUE of the input sequence goes beyond limit (-9223372036854775808 to 9223372036854775807).

Sequence value beyond the limit

If the input MINVALUE or MAXVALUE of a sequence goes beyond the limits (-9223372036854775808 to 9223372036854775807), the sequence will be replaced with the boundary value for centralized GaussDB 1.4 and the LARGE SEQUENCE value for centralized GaussDB 2.0 or later.

GaussDB does not support DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION.

DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION

Do not make any change for DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION and do not migrate the script. An error message will be recorded.

GaussDB does not support the READ ONLY keywords. As a result, the migrated script fails to be compiled.

READ ONLY keywords

Comment the READ ONLY keyword in ALTER TABLE statements.

GaussDB does not support the RECORD VARIABLE used in the INSERT statements.

RECORD VARIABLE

Migrate the script. RECORD VARIABLE in the INSERT statements will be split based on columns.

Versions earlier than GaussDB V5R1C20 do not support AUTONOMOUS TRANSACTION and there is no equivalent.

AUTONOMOUS TRANSACTION

Comment AUTONOMOUS TRANSACTION. This configuration is used for GaussDB V5R1C20 and earlier versions. Compiling the migrated script will not throw any error.

Some GaussDB versions do not support FILE_TYPE.

FILE_TYPE

Migrate the script. FILE_TYPE is converted to NUMBER.

If a table name does not contain schema, duplicate tables exist. As a result, the table fails to be created.

Global temporary tablename conversion

A converted table name is a new name formed by merging schema name and table name separated by a delimiter.

GaussDB V5R1C20 and earlier versions do not support the SAVEPOINT syntax.

SAVEPOINT syntax

Comment SAVEPOINT. This configuration is used for GaussDB V5R1C20 and earlier versions. Compiling the migrated script will not throw any error.

GaussDB V5R2 does not support system privileges. No system privileges will be given.

System privileges

Comment the entire SQL script.

GaussDB does not support the LONG data type. Compiling the migrated script will fail.

LONG data type

Convert LONG to TEXT.

GaussDB does not support the SUB partition.

SUB partition

Migrate the script. The SUB partition is commented.

GaussDB does not support the LONG RAW data type.

LONG RAW data type

Convert LONG RAW to BYTEA.

GaussDB supports the FLOAT data type, which is implicitly converted to DOUBLE PRECISION. In Oracle, the default precision (maximum precision) of FLOAT is 126. In GaussDB, if the FLOAT precision is greater than 53, an error will occur and the statement execution will fail.

FLOAT data type

Convert FLOAT/FLOAT(n) to NUMBER.

GaussDB does not support REF CURSOR used in packages.

REF CURSOR

Migrate the script. An alternate way is used to create REF CURSOR.

The distributed GaussDB does not support the LIST partition.

LIST partition

Ignore the LIST partition.

System roles cannot be granted to the users in GaussDB.

System roles

Comment the entire Grant statement.

GaussDB V5R1 does not support ROWNUM used in stored procedures and DML statements. The distributed GaussDB does not support ROWNUM.

ROWNUM

Convert the script. ROWNUM is covered to LIMIT.

GaussDB V5R2 does not support the HASH partition.

HASH partition

Comment the HASH partition.

For distributed GaussDB, if there are no distribution keys, the execution fails.

Unique constraints and indexes in a distributed (mutli data nodes) environment

Add distribution keys.

The name of the user who creates the synonym is different from the name of the object owner.

Synonym

Add ALTER statement to change the owner details.

GaussDB does not support the GNERATED AS IDENTITY syntax.

DENTITY columns

Convert IDENTITY columns to SERIAL columns.

GaussDB V5R2 does not support CURSOR variables defined in package specifications.

CURSOR variables

Migrate the script. CURSOR variables will be locally created in the package bodies.

GaussDB V5R1 does not support user-defined types.

User-defined types

Covert user-defined types.

GaussDB V5R1C20 and earlier versions do not support the BULK COLLECT syntax.

BULK COLLECT syntax

Convert the BULK COLLECT syntax. This configuration applies to GaussDB V5 R1C20 and earlier versions.

If GaussDB does not support SET_ATTRIBUTE specified by Oracle, the migration will fail. SET_ATTRIBUTE supported by GaussDB includes: START_DATE, REPEAT_INTERVAL, END_DATE, COMMENTS, PROGRAM_TYPE, PROGRAM_ACTION, NUMBER_OF_ARGUMENTS, SCHEDULE_NAME, JOB_TYPE, JOB_ACTION, JOB_CLASS, ENABLED, AUTO_DROP, CREDENTIAL_NAME, DESTINATION_NAME, PROGRAM_NAME, JOB_STYLE.

DBMS_SCHEDULER.SET_ATTRIBUTE

If the given SET ATTRIBUTES is not supported by GaussDB, comment DBMS_SCHEDULER.SET_ATTRIBUTE.

GaussDB does not support REVERSE indexes.

REVERSE keywords

Comment REVERSE

GaussDB V5R1 and GaussDB V5R2 do not support the AUTHID clauses.

AUTHID clauses of the PL/SQL objects

If AUTHID DEFINER/INVOKER is not specified, add AUTHID DEFINER to match the Oracle configurations. This configuration applies to GaussDB V5R2 because this version does not match the Oracle configuration by default.

GaussDB does not support the NCLOB data type.

NCLOB data type

Convert NCLOB to TEXT.

GaussDB does not support the XMLTYPE data type.

XMLTYPE data type

Convert XMLTYPE to JSON.

If functions specified in CREATE INDEX statements are not supported by GaussDB, an error is reported during index creation.

Functions in indexes

Comment the CREATE INDEX statements with unsupported functions.

GaussDB V5R1 does not support the INTERVAL partition.

INTERVAL partition

Comment the INTERVAL partition.

GaussDB does not support external and global users.

Global or external users

Comment the entire CREATE USER statements.

GaussDB does not support the MBRCOORDLIST object type.

MDSYS.MBRCOORDLIST object type

Convert MBRCOORDLIST to TEXT.

GaussDB does not support BITMAP indexes.

BITMAP indexes

Create BTREE indexes instead of BITMAP indexes.

GaussDB does not support global temporary tables.

Global temporary tables

Create local temporary tables

GaussDB does not support the DEFAULT ON NULL clauses.

DEFAULT ON NULL clauses

Comment the ON NULL keywords.

Oracle supports 4 GB of data for the CLOB data type. CLOB data type in GaussDB supports a maximum of 1 GB data.

CLOB data type

Do not convert the CLOB data type. GaussDB supports the CLOB data type. Oracle supports 4 GB of data for the CLOB data type. CLOB data type in GaussDB supports a maximum of 1 GB data.

GaussDB V5R2 does not support SQL PLUS commands.

SQL PLUS

Comment SQL PLUS.

In Oracle, the maximum size of BLOB data type is 4 GB. In GaussDB, the maximum size of the BLOB data type is 530 MB. If the table column data is beyond the limit, the data change operations (INSERT/UPDATE) fail and an error is thrown.

BLOB data type

Do not convert BLOB. GaussDB supports the BLOB data type. Oracle supports 4 GB of data for the BLOB data type. BLOB data type in GaussDB supports a maximum of 530 MB data.

In V5R2C10, GaussDB supports 4 GB of data for the BLOB data type.

GaussDB does not support the GEOMETRY data type.

GEOMETRY data type

Convert GEOMETRY to TEXT.

GaussDB supports only GREGORIAN calendar and it supports only TO_DATE and TO_TIMESTAMP. If the third parameter is specified in TO_DATE and TO_TIMESTAMP, compiling the script will fail.

Calendar type other than GREGORIAN

If the third parameter is specified in TO_DATE and TO_TIMESTAMP, comment GREGORIAN calendar.

GaussDB partially supports PHYSICAL VIEW REFRESH.

PHYSICAL VIEW REFRESH

Comment PHYSICAL VIEW REFRESH.

GaussDB V5R1 does not support hierarchical queries.

Hierarchical queries

Migrate the hierarchical queries.

GaussDB does not support the SDO_GEOMETRY data type.

SDO_GEOMETRY

Convert SDO_GEOMETRY object type to TEXT.

GaussDB does not support the EDITIONING keywords.

EDITIONING keywords

Comment the EDITIONING keywords in view statements.

GaussDB does not support the UNUSED keywords in the ALTER TABLE statements.

UNUSED keywords

Delete unused columns from tables.

GaussDB V5R2 does not support schemas with the same name as packages.

Package names

Migrate the script. Double quotation marks are added to package names.

Tables in Oracle contain NUMBER data type with zero scale, for example, NUMBER(10)

NUMBER data type with zero scale

If the precision of NUMBER is between 1 and 4, it is converted to SMALLINT type. If the precision of NUMBER is between 5 and 9, it is converted to INT type. If the precision of NUMBER is between 10 and 18, it is converted to BIGINT type.

GaussDB does not support the SYS_OP_C2C functions.

SYS_OP_C2C

Comment the names of the SYS_OP_C2C functions.

For natively supported objects, if there are some syntax compatibility problems, it is required to verify them.

Parsing converted SQL statements

Parse the converted SQL statements.

GaussDB does not support the MONITORING clauses in the CREATE TABLE statements.

MONITORING

Comment MONITORING in the CREATE TABLE statements.

GaussDB does not support automatic partitioning for tables.

Automatic partitioning for tables

Comment the automatic partitioning statements.

The ctids in GaussDB supports the conversion of partially rowids in Oracle.

rowid expression

Convert rowids to system column ctids and tableoids.

GaussDB supports only some HINT scenarios.

HINT Tuning

Convert HINT clauses supported by GaussDB and give error message for unsupported HINT clauses.

GaussDB does not support NOLOGGING in partitioned tables.

NOLOGGING in partitioned tables

Comment NOLOGGING.

GaussDB does not support generated columns.

Generated columns as partition keys

Comment the partitioned table scripts and convert tables to non-partitioned tables.

The invoking permission of some GaussDB objects of earlier versions is INVOKER.

AUTHID clauses of the PL/SQL objects

In GaussDB R2, if AUTHID DEFINER/INVOKER is not specified, add AUTHID DEFINER to match the Oracle configurations.

In GaussDB R2, do not add AUTHID DEFINER.

In Oracle, the maximum size of CLOB data type is 4 GB. GaussDB supports the CLOB data type and its maximum size is 1 GB. If the size exceeds the limit, the process fails and an error is reported.

CLOB data type in functions

Do not covert CLOB data type, but its storage is limited.

GaussDB does not all GRANT ANY OBJECT PRIVILEG clauses.

GRANT ANY OBJECT PRIVILEGE clauses

Convert the script based on the target database syntax.