Overview

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

OceanBase Database is compatible with Oracle Database in terms of basic features such as data types, SQL functions, and database objects. In addition, OceanBase Database supports all procedural language (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 not only provides sound compatibility with Oracle Database but even outperforms it in some cases. This means that you can smoothly migrate your data and services from Oracle Database to OceanBase Database without having to spend a lot of time learning everything about OceanBase Database.

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

• SQL data types

• Built-in functions

• SQL syntax

• Procedural languages

• System views

• Character sets and collations

• Database object management

• Security features

• Backup and restore

• SQL engine

• Unsupported features

SQL data types

Oracle Database supports 24 data types and OceanBase Database supports 20 of them. For more information, see SQL data types. However, for optimization purposes, OceanBase Database has no plans to support the LONG and LONG RAW data types, as they are considered outdated.

Built-in functions

Oracle Database supports 257 built-in functions and OceanBase Database supports 155 of them. For more information about the supported functions, 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.

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.

• The following syntax is supported for viewing execution plans:

  EXPLAIN [explain_type] dml_statement;
  explain_type:
      BASIC
    | OUTLINE
    | EXTENDED
    | EXTENDED_NOADDR
    | PARTITIONS
    | FORMAT = {TRADITIONAL| JSON}
  
  dml_statement:
      SELECT statement
    | DELETE statement
    | INSERT statement
    | MERGE INTO statement
    | UPDATE statement
  

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.

  In OceanBase Database, you need to manually specify the degree of parallelism (DOP) by using the hint/session variable. The Auto DOP feature is not supported.

• OceanBase Database supports parallel DML statements.

Hint

Oracle Database supports 72 hints and OceanBase Database is compatible with 24 of them. In addition, OceanBase Database provides 23 exclusive hints.

For more information about hints, see Hint.

Procedural languages

OceanBase Database is compatible with most PL features of Oracle Database, including:

• 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 optimization

• 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 does not support the following PL feature:

• Conditional compilation

For more information about PL features, see PL references.

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 System views.

Character sets and collations

• OceanBase Database supports the following character sets: binary, utf8mb4, gbk, utf16, gb18030, and latin1.

• OceanBase Database supports the following collations: binary, utf8mb4_general_ci, utf8mb4_bin, gbk_chinese_ci, gbk_bin, utf16_general_ci, utf16_bin, utf8mb4_unicode_ci, utf16_unicode_ci, gb18030_chinese_ci, gb18030_bin, and latin1_bin.

Database object management

Table management

• Create tables: OceanBase Database allows you to create tables and specify partitions and constraints when you create a table.

• Modify base tables: OceanBase Database allows you to use the ALTER TABLE statement to add, delete, or modify columns, add or delete constraints, and add, delete, or modify partitions.

• Delete base tables: OceanBase Database allows to you delete tables and the constraints on the tables.

For more information about the syntax for creating, modifying, and deleting tables, see CREATE TABLE, ALTER TABLE, and DROP TABLE.

Constraints

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

• DISABLE operations are not supported for the UNIQUE constraint.

• Foreign key constraints are supported.

• OceanBase Database allows you to add a foreign key constraint by executing the ALTER TABLE statement.

• The SET NULL constraint is not supported 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:

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

• OceanBase Database does not support the addition or dropping of subpartitions for template-based subpartitioned tables.

For more information about partitioning, see Partition overview.

Index management

• Only B-tree is supported.

• OceanBase Database allows you to create and delete indexes.

• Index types such as bitmaps and reverse indexes are not supported.

View management

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

• OceanBase Database allows you to delete views.

• OceanBase Database supports SELECT statements.

• OceanBase Database supports DML statements.

Updatable views

The WITH CHECK OPTION clause is not supported.

Sequence management

OceanBase Database allows you to create, modify, delete, and reset sequences.

Synonyms

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

Trigger management

OceanBase Database allows you to create, modify, and drop a trigger.

DBLinks

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

Security features

OceanBase Database implements a wide range of security features.

Privilege management

• OceanBase Database is compatible with 24 system privileges in Oracle Database.

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

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

• OceanBase Database supports predefined system roles and custom roles.

Identity authentication

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

• User locking and unlocking are supported.

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, but it does not support the unified auditing and Fine-Grained Auditing (FGA) features.

• Statement auditing and object auditing are supported, whereas privilege auditing and network auditing are not supported.

• Object auditing supports only tables, sequences, functions, and packages.

• Auditing results can be stored in files or internal tables.

• OceanBase Database provides various audit-related system views.

• Audit data cannot be deleted.

Label security

• OceanBase Database is compatible with the label security feature of Oracle Database.

• Among the components of Oracle Label Security, OceanBase Database supports levels but not compartments or groups.

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

Backup and restore

OceanBase Database supports the following data backup and restore features:

• Alibaba Cloud OSS and NFS as backup destinations

• Log archiving and compression

• Automatic cleanup of expired backup data

• Backup and restore at the tenant level

OceanBase Database does not support the following backup and restore features:

• Backup and restore at the cluster level

• Backup to the specified path

• Manual cleanup of backup data

• Secondary backup

• Verification of backup data

• Backup and restore for some databases within tenants and backup and restore at the table level

SQL engine

The SQL engine of OceanBase Database is compatible with most features of Oracle Database, including:

• Query rewrite

• Precompiled statements

• Cost-based optimizer

• Execution plan generation and display by using the EXPLAIN statement

• Plan cache

• Fast parameterization of execution plans

• Execution plan binding

• Optimizer hints

• Adaptive cursor sharing (ACS)

• SQL plan management (SPM)

The SQL engine of OceanBase Database does not support the following features:

• Estimator

• Execution plan isolation

• Expression statistics store (ESS)

• Approximate query processing (AQP)

For more information about the SQL engine of OceanBase Database, see SQL tuning.

Unsupported features

• LONG and LONG RAW data types

• Conditional compilation in PL

• DISABLE operations on the UNIQUE constraint, DISABLE and ENABLE operations on foreign key constraints, and the SET NULL constraint for cascaded tables

• Index types such as bitmaps and reverse indexes

• WITH CHECK OPTION clause

• Deletion of audit-related views

• Backup and restore at the database, cluster, and table levels, backup to a specified path, manual cleanup of backup data, secondary backup, verification of backup data, and backup and restore for some databases within tenants

• Estimators, execution plan isolation, ESS, and AQP for the SQL engine of OceanBase Database