Overview

2025-12-02 03:38:07  Updated

This topic describes the compatibility between OceanBase Database's Oracle mode and native Oracle Database.

OceanBase Database is compatible with Oracle Database in terms of basic features, including the data types, SQL features, and database objects. OceanBase Database is compatible with all PL-related R&D features of Oracle Database. In terms of advanced features such as database security, backup and restore, high availability, and optimizer, OceanBase Database provides sound compatibility with and even outperforms Oracle Database. Therefore, you can smoothly migrate data and services from Oracle Database to OceanBase Database without spending much time learning everything about OceanBase Database.

However, due to the differences in the underlying architecture and product form between the two, OceanBase Database does not support some features of Oracle Database and differs from it in some aspects. This topic describes the compatibility between OceanBase Database and Oracle Database in the following aspects:

  • SQL data types

  • Built-in functions

  • SQL syntax

  • Procedural languages

  • System views

  • Character sets

  • Collations

  • Database object management

  • Security features

  • Backup and restore

  • SQL engine

SQL data types

OceanBase Database is compatible with most of the data types supported by Oracle Database. For more information, refer to SQL data types. For optimization purposes, the LONG and LONG RAW data types are considered outdated and are not currently supported by OceanBase Database.

Built-in functions

OceanBase Database is compatible with most of the built-in functions supported by Oracle Database. For more information, see Built-in functions.

SQL syntax

OceanBase Database supports most of the SQL syntax in Oracle Database. For some syntax, OceanBase Database will return an "unsupported syntax" error because the corresponding features are unavailable.

Supported

SELECT

  • OceanBase Database supports most query features, including single- and multi-table queries, subqueries, inner-join, semi-join, outer-join, grouping, aggregation, hierarchical queries, and regular data mining functions such as probability and linear regression.

  • The following set operators are supported: UNION, UNION ALL, INTERSECT, and MINUS.

  • OceanBase Database supports using the EXPLAIN syntax to view the execution plan.

INSERT

  • OceanBase Database supports single- and multi-row insert. It also supports inserting data into a specified partition.

  • OceanBase Database supports the INSERT INTO … SELECT … statement.

  • OceanBase Database supports single- and multi-table inserts.

UPDATE

  • OceanBase Database supports single- and multi-column updates.

  • OceanBase Database supports updates by using subqueries.

  • OceanBase Database supports set updates.

DELETE

  • OceanBase Database supports single- and multi-table deletion.

TRUNCATE

  • OceanBase Database allows you to truncate a specified table.

Parallel query

  • OceanBase Database supports parallel queries similar to those in Oracle Database.

    OceanBase Database supports the auto degree of parallelism (DOP) feature. You can also manually specify the DOP by using hints or session variables.

  • OceanBase Database supports parallel DML statements.

Hints

OceanBase Database supports the use of hint syntax. For more information about hints, refer to Hint.

Procedural languages

OceanBase Database is compatible with most PL functions in Oracle Database. For more information about PL functions, see PL reference.

Supported
Not supported

The main PL features supported by OceanBase Database are as follows:

  • Data types

  • Process control

  • Sets and records (multi-dimensional sets are not supported)

  • Static SQL statements

  • Dynamic SQL statements

  • Subprocedures

  • Triggers

  • Exception handling

  • Packages

  • Performance tuning

  • Custom data types

  • PL packages, including DBMS_CRYPTO, DBMS_DEBUG, DBMS_LOB, DBMS_LOCK, DBMS_METADATA, DBMS_OUTPUT, DBMS_RANDOM, DBMS_SQL, DBMS_XA, UTL_I18N, and UTL_RAW

  • PL tag security packages, including SA_SYSDBA, SA_COMPONENTS, SA_LABEL_ADMIN, SA_POLICY_ADMIN, SA_USER_ADMIN, and SA_SESSION

OceanBase Database has compatibility differences with Oracle Database in implementing the behavior of the NOCOPY parameter:

  • If the parameter is of a basic data type or the parameter has the OUT attribute and is of a complex data type, OceanBase Database is incompatible with the pass-by-reference behavior in Oracle Database.

  • If the parameter has the IN OUT attribute and is of a complex data type, OceanBase Database is compatible with the pass-by-reference behavior in Oracle Database.

OceanBase Database does not support the following PL feature:

  • Conditional compilation

System views

OceanBase Database is compatible with some system views of Oracle Database, including:

  • 195 dictionary views

  • 20 performance views whose names are prefixed with V$

For a detailed list of compatible views, see System views.

For more information about fields of system views, see Overview of system views.

Character sets

The Oracle mode of OceanBase Database supports the following character sets:

  • binary

  • utf8mb4

  • gbk

  • utf16

  • gb18030

  • latin1

  • gb18030_2022

Collations

The following table lists the collations supported by the Oracle mode of OceanBase Database. | Collation | Character set | Description | |--------------------|---------|-----------------------------------------------| | utf8mb4_general_ci | utf8mb4 | A general collation. | | utf8mb4_bin | utf8mb4 | A binary collation. | | utf8mb4_unicode_ci | utf8mb4 | A collation that is based on Unicode Collation Algorithm (UCA). | | binary | binary | A binary collation. | | gbk_chinese_ci | gbk | A collation for Chinese. | | gbk_bin | gbk | A binary collation. | | utf16_general_ci | utf16 | A general collation. | | utf16_bin | utf16 | A binary collation. | | utf16_unicode_ci | utf16 | A collation that is based on UCA. | | gb18030_chinese_ci | gb18030 | A collation for Chinese. | | gb18030_bin | gb18030 | A binary collation. | | latin1_bin | latin1 | A binary collation. | | gb18030_2022_bin | gb18030_2022 | A binary collation. This is the default collation for this character set in Oracle mode. | | gb18030_2022_chinese_ci | gb18030_2022 | A collation that sorts data by Pinyin. The collation is case-insensitive. | | gb18030_2022_chinese_cs | gb18030_2022 | A collation that sorts data by Pinyin. The collation is case-sensitive. | | gb18030_2022_radical_ci | gb18030_2022 | A collation that sorts data by radicals. The collation is case-insensitive. | | gb18030_2022_radical_cs | gb18030_2022 | A collation that sorts data by radicals. The collation is case-sensitive. | | gb18030_2022_stroke_ci | gb18030_2022 | A collation that sorts data by strokes. The collation is case-insensitive. |

| gb18030_2022_stroke_cs | gb18030_2022 | A collation that sorts data by strokes. The collation is case-sensitive. |

Database object management

Supported
Not supported

Table management

  • Create tables: OceanBase Database allows you to create tables and specify partitions and constraints when you create a table. For more information about the syntax for creating tables, see CREATE TABLE.

  • Modify base tables: OceanBase Database allows you to use the ALTER TABLE statement to add, drop, or modify columns, add or drop constraints, and add, drop, or modify partitions. For more information about the syntax for modifying tables, see ALTER TABLE.

  • Drop base tables: OceanBase Database allows to you drop tables and the constraints on the tables. For more information about the syntax for dropping tables, see DROP TABLE.

Constraints

  • OceanBase Database supports the CHECK, UNIQUE, and NOT NULL constraints.

  • OceanBase Database supports the PRIMARY KEY constraint.

  • OceanBase Database supports the FOREIGN KEY constraint.

  • OceanBase Database allows you to add CHECK, PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints by executing the ALTER TABLE statement.

  • OceanBase Database supports the SET NULL constraint for cascaded tables.

Partition support

  • OceanBase Database supports partitioning, template-based subpartitioning, and non-template-based subpartitioning.

  • OceanBase Database supports HASH, RANGE, LIST, and composite partitioning.

  • OceanBase Database supports local and global indexes.

    • For partition maintenance, note that:

    • You can add, drop, and truncate partitions for partitioned tables.

  • You can add and drop partitions for template-based subpartitioned tables, and add, drop, and truncate partitions and subpartitions for non-template-based subpartitioned tables.

For more information about partitions, see Overview.

  • OceanBase Database supports only B-tree indexes and function-based indexes.

  • OceanBase Database supports only B-tree indexes.

  • OceanBase Database allows you to create and drop indexes.

View management

  • OceanBase Database allows you to create simple and complex views.

  • OceanBase Database allows you to drop views.

  • OceanBase Database supports SELECT statements.

  • OceanBase Database supports DML statements.

Sequence management

  • OceanBase Database allows you to create sequences.

  • OceanBase Database allows you to modify sequences. For example, you can reset sequences.

  • OceanBase Database allows you to drop sequences.

Synonyms

  • OceanBase Database allows you to create synonyms for objects such as tables, views, synonyms, and sequences.

  • OceanBase Database allows you to create public synonyms.

Trigger management

  • OceanBase Database allows you to create triggers.

  • OceanBase Database allows you to modify triggers.

  • OceanBase Database allows you to drop triggers.

DBLinks

  • OceanBase Database supports read and write operations on an OceanBase database by using a DBLink.

  • OceanBase Database supports read and write operations on an Oracle database by using a DBLink.

Constraints

  • OceanBase Database does not support DISABLE operations for the UNIQUE constraint.

Partition support

  • OceanBase Database does not allow you to add or drop subpartitions for template-based subpartitioned tables.

Index management

  • When you use the UPDATE GLOBAL INDEX statement to rebuild an index, the index is unavailable during the rebuilding process.

  • OceanBase Database does not support index types such as bitmap indexes or reverse indexes.

Updatable views

OceanBase Database does not support the WITH CHECK OPTION clause.

Security features

OceanBase Database implements a wide range of security features.

Supported
Not supported

Privilege management

  • OceanBase Database is compatible with most system privileges in Oracle Database. For more information about the privileges supported by the Oracle mode of OceanBase Database, see Privilege types in Oracle mode.

  • OceanBase Database supports regular object privilege management operations such as privilege granting and revocation.

  • OceanBase Database supports allowlist configuration for secure network access control.

  • OceanBase Database supports predefined system roles and custom roles.

Identity authentication

  • The Oracle mode of OceanBase Database is compatible with the password strategies of Oracle Database.

  • OceanBase Database supports user locking and unlocking.

Transparent data encryption

OceanBase Database is compatible with the transparent data encryption (TDE) feature of Oracle Database. Data is automatically encrypted before it is written to storage devices and automatically decrypted when it is read.

Auditing

  • OceanBase Database supports the standard auditing feature of Oracle Database.

  • OceanBase Database supports statement auditing and object auditing.

  • OceanBase Database supports only tables, sequences, functions, and packages for object auditing.

  • OceanBase Database allows to you store audit data in files or internal tables.

  • OceanBase Database provides various audit-related system views.

Label security

  • OceanBase Database is compatible with the Oracle Label Security feature of Oracle Database.

  • OceanBase Database supports the level component of Oracle Label Security.

SSL-based transmission link encryption

  • OceanBase Database offers the ability to encrypt data transmission both between clients and OBServer nodes and between OBServer nodes themselves.

  • OceanBase Database supports one-way Secure Sockets Layer (SSL) authentication, two-way X509 authentication, as well as additional special two-way authentication mechanisms, such as specifying the encryption algorithm, issuer, and SSL subject for authentication.

Auditing

  • OceanBase Database does not support unified auditing or fine-grained auditing (FGA).

  • OceanBase Database does not support privilege auditing or network auditing.

  • OceanBase Database does not allow you to delete audit data.

Label security

  • OceanBase Database does not support the compartment component or group component of Oracle Label Security.

Backup and restore

OceanBase Database also supports backup and restore features. For more information about the physical backup and restore features of OceanBase Database, see Overview.

Supported
Not supported

  • OceanBase Database allows you to use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Amazon Simple Storage Service (S3), and object storage services compatible with the S3 protocol as the backup media.

  • OceanBase Database supports automatic cleanup of expired backups.

  • OceanBase Database supports backup and restore at the tenant level.

  • OceanBase Database supports restore at the database and table levels.

  • OceanBase Database does not support backup and restore at the cluster level.

  • OceanBase Database does not support backup at the table level.

  • OceanBase Database does not support compression for log archives.

  • OceanBase Database does not support backup to a specified path.

  • OceanBase Database does not support manual cleanup of backups.

  • OceanBase Database does not support secondary backup.

  • OceanBase Database does not support the verification of backup data.

SQL engine

The SQL engine of OceanBase Database is compatible with most features of Oracle Database. For more information about the SQL engine of OceanBase Database, see SQL tuning.

Supported
Not supported

  • OceanBase Database supports query rewriting.

  • OceanBase Database supports precompiled statements.

  • OceanBase Database supports the cost-based optimizer.

  • OceanBase Database allows you to generate and display execution plans by using the EXPLAIN statement.

  • OceanBase Database supports the plan cache.

  • OceanBase Database supports fast parameterization of execution plans.

  • OceanBase Database supports execution plan binding.

  • OceanBase Database supports optimizer hints.

  • OceanBase Database supports adaptive cursor sharing (ACS).

  • OceanBase Database supports SQL plan management (SPM).

  • OceanBase Database does not support the estimator.

  • OceanBase Database does not support execution plan isolation.

  • OceanBase Database does not support expression statistics store (ESS).

  • OceanBase Database does not support approximate query processing (AQP).

Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartnerTrust Center

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite Paper

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us