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, including the data types, SQL capabilities, 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 language (PL)

• System views

• Character sets and collations

• Database object management

• Security features

• Backup and restore

• High availability

• 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. OceanBase Database does not support the LONG and LONG RAW data types because they are outdated.

Note

OceanBase Database supports large object (LOB) data types sized no more than 48 MB. Due to the poor performance of LOB data types, we recommend that you do not use them in complex scenarios. For more information, see LOB data types.

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

TRUNCATE

• OceanBase Database allows you to truncate a specified table.

Parallel querying

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

Hints

OceanBase Database supports hints. 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 Overview of hints.

PL

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 (You can create triggers only on tables but not on views.)

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

• 186 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 in system views, see topics in System views.

Character sets and collations

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

• 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, and gb18030_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, drop, or modify columns, add or drop constraints, and add, drop, or modify partitions.

• Drop base tables: OceanBase Database allows to you drop tables and the constraints on the tables.

For more information about the syntax for creating, modifying, and dropping 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.

• DISABLE and ENABLE operations are not supported for foreign key constraints.

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

• Template-based and non-template-based subpartitioned table groups are supported.

For more information about partitioning, see Partition overview.

Index management

• Only B-tree is supported.

• OceanBase Database allows you to create and drop 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 drop 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, drop, 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.

Database links

OceanBase Database supports read-only operations on database links from an OceanBase database to an OceanBase or Oracle database.

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.

TDE

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 transmission link encryption

• OceanBase Database supports data encryption for transmission between clients and OBServer nodes and transmission between OBServer nodes.

• OceanBase Database supports one-way SSL authentication, two-way X509 authentication, and some special two-way authentication mechanisms, such as authentication with a specified encryption algorithm, specified issuer, and specified SSL subject.

Backup and restore

OceanBase Database supports the following backup and restore features:

• Using Alibaba Cloud OSS, NFS, and Tencent Cloud COS as backup destinations

• Full backup, incremental backup, and log backup at the cluster level

• Log archiving and compression

• Backup expiration time settings

• Automatic and manual cleanup of expired backup data

• Manual clearing of specified backups at the cluster level

• Querying for information about backup files and ongoing backup tasks

• Restore at the tenant level

• Secondary backup at the cluster and tenant level

• Restore of tables in the allowlist of a tenant

• Verification of backup data

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

• Manual cleanup of specified backups at the tenant level

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

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

High availability

OceanBase Database uses multiple replicas to implement high availability and supports the following features:

• Compatible with the Data Guard feature of Oracle Database and the maximum availability mode, maximum performance mode, and maximum protection mode for primary and standby clusters.

• Supports both logical backup and physical backup for higher data security. This feature is similar to Oracle Recovery Manager (RMAN).

For more information about the high availability feature of OceanBase Database, see Overview.

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

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

• Bitmap indexes and reverse indexes

• The WITH CHECK OPTION clause

• Dropping of audit-related views

• Manual cleanup of specific backup data at the tenant level, backup and restore at the table level or of a part of a database in a tenant

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