Editing the Configurations During Migration from Oracle to GaussDB

When table structures of all Oracle features are 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, indexes, 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, INTERVAL, 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.

For Oracle tables with the UNLOG attribute, logs can be recorded when the log level is FORCE LOGGING. However, after UNLOG is specified for GaussDB, logs are not recorded. As a result, data cannot be restored.

NOLOGGING

If logs are not required, convert NOLOGGING to UNLOGGED.

Impact: GaussDB does not support NOLOGGING tables. This configuration converts NOLOGGING to UNLOGGED. After the conversion, the performance is not affected. The GAUSSDB UNLOGGED tables do not record logs.

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.

Special character object names case format

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.

Reserved keyword object names case format

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. If an object name contains reserved keywords, the object name remains unchanged and the double quotation marks (") of the object name are not retained. The error information is recorded.

Primary/standby GaussDB 1.4 and all distributed GaussDB versions do not support the PACKAGE syntax.

Package

Merge the package names along with the stored procedure names.

Primary/standby GaussDB 1.4 and all distributed GaussDB versions do not support FOREIGN KEY constraint.

FOREIGN KEY constraints

Comment FOREIGN KEY constraints.

GaussDB does not support CHARACTER_SET.

CHARACTER_SET

Comment CHARACTER_SET.

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

Mismatch character set. (When converting SQL statement conversion, you need to configure the character sets of the source and target databases.)

Adjust the sizes of CHAR(n), VARCHAR(n), VARCHAR2(n), and NCHAR(n).

GaussDB 1.4 does not support the 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.

Case conversion of system view objects

Convert object names to lowercase letters.

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

Object-level privileges

Grant schema-level privileges.

GaussDB supports TABLESPACE clauses 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 1.4 does not support the 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 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.

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 1.4 does not support the READ ONLY keywords. As a result, the migrated script fails to be compiled.

READ ONLY keywords in tables

Create a trigger to support the READ ONLY mode.

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

RECORD VARIABLE in INSERT statements

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

GaussDB 1.4 does 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.

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 1.4 does 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 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.

Primary/standby GaussDB 1.4 and all distributed GaussDB versions do not support the SUB partition.

SUB partition

Convert 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. If you want to retain the FLOAT type and want the precision to be greater than 53 or be empty, convert FLOAT/FLOAT(n) to FLOAT(53).

The distributed GaussDB 3.1 and earlier versions do not support the LIST partition.

LIST partition

Comment LIST partitions.

System roles cannot be granted to the users in GaussDB.

System roles

Comment the entire Grant statement.

The distributed GaussDB 3.1 and earlier versions do not support ROWNUM used in stored procedures and DML statements.

ROWNUM

Convert the script. ROWNUM is covered to LIMIT.

Primary/standby GaussDB 1.4 and all distributed GaussDB versions do not support Oracle hash partitions.

HASH partition

Comment the HASH partition.

For distributed GaussDB, if there are no distribution keys, the execution will fail in GaussDB 2.7 or earlier. In GaussDB 3.2 or later, a global secondary index will be created.

The unique constraint or unique index does not contain a distribution key.

If the unique index does not contain a distribution key, then comment out the unique keyword; if the unique constraint does not contain a distribution key, then comment out the unique constraint and create the associated regular index.

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.

IDENTITY columns

Convert IDENTITY columns to SERIAL columns.

GaussDB V5R1 does not support user-defined types.

User-defined types

Covert user-defined types.

GaussDB 1.4 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 or TEST.

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.

Distributed 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.

In Oracle, the maximum size of CLOB data type is 4 GB. GaussDB supports the CLOB data type. In primary/standby GaussDB 2.7 and later versions, a maximum of 32 TB data can be stored. In other versions, a maximum of 1 GB data can be stored. If the value exceeds the threshold, an error is reported.

CLOB data type

Do not convert the CLOB data type. In Oracle, the maximum size of CLOB data type is 4 GB. GaussDB supports the CLOB data type. In primary/standby GaussDB V5R2C10 and later versions, a maximum of 32 TB data can be stored. In other versions, a maximum of 1 GB data can be stored.

GaussDB does not support SQL PLUS commands.

SQL PLUS

Comment SQL PLUS.

In Oracle, the maximum size of BLOB data type is 4 GB. GaussDB supports the BLOB data type. In primary/standby GaussDB 2.7 and later versions, a maximum of 32 TB data can be stored. In other versions, a maximum of 1 GB data can be stored. 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. In Oracle, the maximum size of BLOB data type is 4 GB. GaussDB supports the BLOB data type. In primary/standby GaussDB V5R2C10 and later versions, a maximum of 32 TB data can be stored. In other versions, a maximum of 1 GB data can be stored.

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 does not allow you to specify attributes such as Creation Mode and Query Rewriting in materialized views.

Related attributes in materialized views

Comment clauses that are not supported in materialized views.

GaussDB V5R1 does not support hierarchical queries.

Hierarchical queries

Convert 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.

Primary/standby GaussDB 1.4 and all distributed GaussDB versions do not support the same name of the Oracle schema and package.

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. In primary/standby GaussDB 2.7 and later versions, a maximum of 32 TB data can be stored. In other versions, a maximum of 1 GB data can be stored. If the data 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 support all GRANT ANY OBJECT PRIVILEG clauses.

GRANT ANY OBJECT PRIVILEGE clauses

Convert the script based on the target database syntax.

For a table with both a partition key and a unique index, the partition key must appear in the unique index.

Table column partition with unique constraint

Add partition columns to the unique constraints (primary keys and unique keys).

GaussDB does not support external tables.

External table conversion

Comment external tables and convert them to ordinary tables.

WHERE conditions in SQL statement contain COL=FUNC(), and the service execution takes a long time.

WHERE conditions

Optimize WHERE conditions.

Distributed GaussDB 8.0 does not support IDENTITY columns.

IDENTITY columns

Do not convert IDENTITY columns.

GaussDB does not have the dual system catalog, but has the equivalent sys_dummy system catalog.

dual system catalog

Convert it to SYS_DUMMY.

Primary/standby GaussDB does not support user-defined exceptions in packages.

Adding user-defined exceptions to the subprogram declaration

Add the exception definition used in the subprogram to the subprogram header.

The sizes of the INTEGER, INT, and SMALLINT types in GaussDB are different from those in Oracle. INTEGER is synonyms for NUMBER(38) in Oracle.

INTEGER data type

Convert INT, INTEGER, and SMALLINT to NUMBER(38). To retain INT, INTEGER, and SMALLINT types, do not convert them.

Impacts:

In Oracle, INT, INTEGER, SMALLINT are synonyms for NUMBER(38). In GaussDB, the value range of INT and INTEGER is -2147483648 to 2147483647, and the value range of SMALLINT is -32768 to 32767. If the data in a table column exceeds the range, the data cannot be inserted or modified and an error will occur.

GaussDB does not support the keyword NOCOPY keyword. If there is the keyword NOCOPY, packages fail to be created.

NOCOPY keyword

Comment the keyword NOCOPY to ensure that package objects can be migrated properly.

Impact: GaussDB does not support the keyword NOCOPY. This configuration item will comment the keyword.

GaussDB does not support the UPDATING('column') syntax in Oracle triggers.

UPDATING('column') syntax

Convert UPDATING('column') to (TG_OP = 'UPDATE') AND (NEW.column <> OLD.column).

Impact: If the column value does not change before and after the update, the result of the UPDATING('column') condition in Oracle is True, but the result of the (NEW.column <> OLD.column) condition in GaussDB is False.

After CACHE is defined in a sequence, the sequence continuity cannot be ensured, which may cause number hopping.

CACHE in SEQUENCE

Recommended value: To ensure sequence continuity, convert the value of CACHE to 1, indicating that only one value can be generated at a time, that is, there is no cache.

Impact: If the sequence is used frequently, this configuration will require disk access each time the sequence value is fetched, which may degrade performance.

Each list partition of GaussDB supports a maximum of 64 key values. If the number of key values exceeds 64, an error occurs and the table fails to be created.

Key value of the list partition exceeds the limit

Recommended value: If the number of key values in a partition exceeds 64, a new partition is automatically created and the excess key values are stored in the new partition.

Impact: An error may occur when you specify a partition in a partitioned table, but the entire table is not affected.

DML operations are not blocked during index creation.

CONCURRENTLY clause in CREATE INDEX statement

Recommended value: Add the keyword CONCURRENTLY.

Impact: When such indexes are created, there may be deadlocks. It takes longer to create an Astore table index than a normal table index.

GaussDB reserves the case of object names and uses double quotation marks to quote object names.

Common object names case (non-keywords and without special characters) format

Recommended value: Object name is stored as lowercase in the target database.

Impact: In GaussDB, when creating objects, if the object name is not enclosed in double quotation marks (""), it is stored in lowercase by default.

GaussDB does not support CHECK OPTION clauses in views.

CHECK OPTION clauses

Migrate CHECK OPTION clauses.

In distributed GaussDB, if no distribution keys are specified for a table and the column type does not meet the HASH distribution requirements, the table cannot be created.

If the conditions for creating a distribution key by default are not met, change the table to a replication table.

Recommended value: Convert a table to a replication table.

Impact: If the distribution mode is set to REPLICATION, each row of the table is stored in all DNs. That is, each DN has complete table data, which may increase the data storage size.