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 functions, and database objects. OceanBase Database is compatible with all PL-related R&D features of Oracle Database. In terms of the advanced features such as database security, backup and recovery, 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 and collations

• Database object management

• Security features

• Backup and recovery

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

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 INSERT INTO … SELECT … statements.

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

UPDATE

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

• OceanBase Database supports update 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

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.

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

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.

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

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.

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.

Database links

OceanBase Database supports read-only operations on database links between two OceanBase databases.

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 whitelist 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 supports data encryption for transmission between clients and OBServers and transmission between OBServers.

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

OceanBase supports the following data backup and recovery features:

• Using Alibaba Cloud OSS and NFS as backup destinations

• Log archiving and compression

• Automatic cleanup of expired backup data

• Backup and recovery at the tenant level

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

• Backup and recovery at the cluster level

• Backup to the specified path

• Manual cleanup of backup data

• Secondary backup

• Verification of backup data

• Backup and recovery at the database or table level

High availability

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

• Physical backup. This feature enhances data security and is similar to Oracle Recovery Manager (RMAN).

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)

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 are not supported.

• The WITH CHECK OPTION clause is not supported.

• Deletion of audit-related views

• Backup and recovery at the database, cluster, and table level, backup to the specified path, manual cleanup of backup data, secondary backup, and verification of backup data

• Primary/Standby mode

• Estimators, execution plan isolation, Expression Statistics Store (ESS), and approximate query processing (AQP) for the SQL engine of OceanBase Database