Compatibility with Oracle Database

This topic describes the compatibility between OceanBase Database in Oracle mode and the 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 basically compatible with all the PL-related R&D features of Oracle Database. In terms of the advanced features such as database security, backup and restoration, high availability, and optimizers, OceanBase Database provides sound compatibility with and even more superior features than Oracle Database. Therefore, you can smoothly migrate data and services from Oracle Database to OceanBase Database without spending much time on getting to know OceanBase Database.

However, due to the difference in the underlying architecture and product form between the two, OceanBase Database is not compatible with some features of Oracle Database and differs from Oracle Database 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 restoration

• High availability

• SQL engine

• Unsupported features

SQL data types

Oracle Database supports 24 data types, while OceanBase Database supports 18 data types. For more information, see SQL data types. OceanBase Database does not support outdated data types such as LONG and LONG RAW. Note

OceanBase Database supports large object (LOB) data types sized no more than 48 MB with inadequate performance. Therefore, we recommend that you do not use LOB data types in complex scenarios. For more information, see LOB data types

Built-in functions

Oracle Database supports 117 built-in functions, while OceanBase database supports 103 functions. 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 report an "unsupported syntax" error because the corresponding features are available.

SELECT

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

• OceanBase Database supports the following set operations:

  UNION, UNION ALL, INTERSECT, and MINUS

• You can run the following statement to view an execution plan:

  EXPLAIN <SQL Statement>;
  EXPLAIN extended <SQL Statement>
  

• pivot and unpivot clauses are not supported.

INSERT

• OceanBase Database supports single- and multi-row insertion, and supports data insertion into a specified partition.

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

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

UPDATE

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

• OceanBase Database supports update by using sub queries.

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

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

  In OceanBase Database, DOP needs to be manually specified by using the hint/session variable. The Auto DOP feature is not supported.

• PDML is not supported.

Hint

OceanBase supports hints. Oracle Database supports 73 hints, and OceanBase Database is compatible with 25 of them. In addition, OceanBase Database provides another 20 particular hints.

For more information about hints, choose Basic elements > Annotations > Hint in SQL Reference (Oracle Mode).

Procedural languages

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

• Data types

• Process control

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

• Static SQL statements

• Dynamic SQL statements

• subprocedures

• Triggers

  • OceanBase Database supports only row-level triggers.

  • You can create triggers on tables only but not on views.

  • DISABLE and ENABLE operations are not supported on 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 features:

• Conditional compilation

System views

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

• 149 dictionary views

• 16 performance views, which start with v$

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

For more information about the system views, see the System views chapter in Reference Guide (Oracle Mode).

Character sets and collations

• OceanBase Database supports character sets such as binary, utf8mb4, gbk, utf16, and gb18030.

• OceanBase supports collations such as utf8mb4_bin, gbk_bin, utf16_bin, 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 the table.

• Modify base tables: OceanBase Database allows you to use ALTER TABLE statements 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 CHECK, UNIQUE, and NOT NULL constraints.

• DISABLE operations are not supported on UNIQUE constraints.

• Foreign key constraints are supported.

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

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

• SET NULL is not supported in cascading.

Partition support

• OceanBase Database supports partitions, template-based subpartitions, and non-template-based subpartitions.

• 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 addition or dropping of subpartitions for template-based subpartitioned tables.

• OceanBase Database does not support SPLIT, MERGE, or EXCHANGE operations on partitions.

For more information about partitioning, see "Manage partitioned tables and partitioned indexes" in Administrator Guide.

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.

Index management

• Only B-tree is supported.

• OceanBase Database allows you to create and delete indexes.

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

Database links

OceanBase Database does not support database links.

Synonyms

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

Updatable views

The WITH CHECK OPTION clause is not supported.

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 common object privilege management operations such as privilege granting and revocation.

• OceanBase Database supports whitelist policies 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 policies of Oracle Database.

• User locking and unlocking are supported.

Transparent data encryption (TDE)

OceanBase Database is compatible with the 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, and does not support the unified auditing and Fine Grained Auditing (FGA) features.

• Statement auditing and objecting auditing are supported, while privilege auditing and network auditing are not supported.

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

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

• OceanBase Database supports the level component but not the compartment and group components

SSL transmission link encryption

• OceanBase Database supports data encryption for transmission between clients and OBServers and between OceanBase Database 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, or specified SSL subject.

Backup and restoration

OceanBase supports the following data backup and restoration features:

• 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

• Querying for information about backup files and ongoing backup tasks

• Splitting of the backup directory

• Secondary backup at the cluster and tenant level

• Automatic and manual clearing of expired backups

• Manual clearing of specified backups at the cluster level

• Automatic and manual clearing of expired secondary backups

• Restoration of secondary backups at the tenant level

• Restoration based on a tenant-level whitelist

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

• Data backup at the tenant level

• Manual deletion of specified backups at the tenant level

• Validity verification of backup data

• Database- and table-level backup and restoration

For more information about the physical data backup and restoration features of OceanBase Database, see Backup and restoration in OceanBase Database 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 supports the maximum availability mode, maximum performance mode, and maximum protection mode for primary and standby databases.

• 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 High availability in OceanBase Database 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, see Execution process of SQL queries.

Unsupported features

• LONG and LONG RAW data types

• pivot and unpivot clauses in the SELECT statement

• PDML in parallel queries

• Triggers on views and DISABLE and ENABLE operations on triggers

• Conditional compilation in PL

• DISABLE operations on UNIQUE constraints, DISABLE and ENABLE operations for foreign key constraints, and SET NULL operations on constraints referenced by other tables

• SPLIT, MERGE, and EXCHANGE operations on partitions

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

• Database links

• WITH CHECK OPTION clauses

• Deletion of audit-related views

• Backup at the tenant level, manual deletion of specific backup data at the tenant level, validity verification on backup data, and backup and restoration at the database and table level

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