Overview of Compatibility with Oracle

SELECT

• Supports most query features, including single-table and multi-table queries; subqueries; inner joins, semi joins, and outer joins; grouping and aggregation; hierarchical queries; and common data mining functions such as probability and linear regression.

• Supports the following set operations: UNION, UNION ALL, INTERSECT, and MINUS.

• Supports using the EXPLAIN statement to view execution plans.

INSERT

• Supports single-row and multi-row data insertion, including inserting data into specified partitions.

• Supports the INSERT INTO ... SELECT ... statement.

• Supports inserting data into single and multiple tables.

UPDATE

• Supports single-column and multi-column data updates.

• Supports updating data using subqueries.

• Supports set updates.

DELETE

• Supports single-table and multi-table deletion.

TRUNCATE

• Supports completely truncating a specified table.

Parallel query

• Supports parallel queries similar to those in Oracle Database.

  OceanBase Database supports the Auto DOP feature. You can also manually specify the DOP using Hint/Session variables.

• Supports parallel Data Manipulation Language (DML).

Hint

OceanBase Database supports using Hint syntax. For detailed information about Hints, see Hint.

Materialized View

• CREATE MATERIALIZED VIEW (creates a materialized view).
• DROP MATERIALIZED VIEW (drops a materialized view).

Materialized View Log

• CREATE MATERIALIZED VIEW LOG (creates a materialized view log).

  Note

  In OceanBase Database, the default behavior of the with_clause (PRIMARY KEY, ROWID, SEQUENCE) in the CREATE MATERIALIZED VIEW LOG syntax is incompatible with Oracle Database.

• DROP MATERIALIZED VIEW LOG (drops a materialized view log).

OceanBase Database supports the following PL features:

• Data types

• Process control

• Collections and records (multi-dimensional collections are not supported).

• Static SQL

• Dynamic SQL

• Subprograms

• Triggers

• Exception handling

• Packages

• Performance optimization

• Custom data types

• PL system 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 label 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 NOCOPY parameters:

• For basic data type parameters and complex data type parameters with the OUT attribute only, OceanBase Database is not compatible with the pass-by-reference behavior of Oracle Database.

• For complex data type parameters with the IN OUT attribute, OceanBase Database is compatible with the pass-by-reference behavior of Oracle Database.

Table management

• Create tables: You can create tables and specify partitions, constraints, and other information during creation. For detailed syntax on creating tables, see CREATE TABLE.

• Modify base tables: You can use the ALTER TABLE statement to add, drop, or modify columns; add or drop constraints; and add, drop, or modify partitions. For detailed syntax on modifying tables, see ALTER TABLE.

• Drop base tables: You can drop tables with cascading constraints. For detailed syntax on dropping tables, see DROP TABLE.

Constraints

• Supports the CHECK, UNIQUE, and NOT NULL constraints.

• Supports the PRIMARY KEY constraint.

• Supports the FOREIGN KEY constraint.

• Supports adding CHECK, PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints using the ALTER TABLE statement.

• Supports SET NULL in cascading operations.

Partition support

• Supports partitions, template-based subpartitions, and non-template-based subpartitions.

• Supports partitioning methods such as hash (Hash), range (Range), list (List), and composite partitioning.

• Supports local indexes and global indexes.

• For partition maintenance operations:

  • Partitioned tables support adding, dropping, and truncating partitions.
  • Subpartitioned tables support adding and dropping subpartitions.
  • Subpartitioned tables also support adding, dropping, and truncating subpartitions.

For more information about partitions, see Partition overview.

Index management

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

• Supports creating and dropping indexes.

View management

• Supports creating simple or complex views.

• Supports dropping views.

• Supports the SELECT statement.

• Supports DML statements.

Sequence management

• Supports creating sequences.

• Supports modifying sequences, including the ability to reset sequence values.

• Supports dropping sequences.

Synonyms

• Supports creating synonyms for objects such as tables, views, synonyms, and sequences.

• Supports creating public synonyms.

Trigger management

• Supports creating triggers.

• Supports modifying triggers.

• Supports dropping triggers.

Database links

• Supports read and write operations from OceanBase Database to OceanBase Database.

• Supports read and write operations from OceanBase Database to Oracle Database.

Privilege management

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

• Supports common object privilege management, such as granting and revoking privileges.

• Supports allowlist policies to implement network access control.

• Supports system-defined roles and user-defined roles.

Identity authentication

• Oracle-compatible mode is compatible with the password policies of Oracle Database.

• Supports locking and unlocking users.

Transparent data encryption

Compatible with the TDE feature of Oracle Database. Data is automatically encrypted before being written to storage devices and automatically decrypted upon read.

Auditing

• Supports standard auditing in Oracle Database.

• Supports statement-level and object-level auditing.

• Object-level auditing currently supports tables, sequences, functions, and package objects.

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

• Supports various system views related to auditing.

Label Security

• Compatible with the Label Security feature in Oracle Database.

• Supports the Level dimension.

SSL for transmission link encryption

• Supports transmission link encryption between clients and OceanBase Database servers, as well as data transmission encryption between OceanBase Database nodes.

• In addition to supporting SSL one-way authentication and X509 two-way authentication, it also supports special two-way authentications such as specified encryption algorithms, specified issuer authentication, and specified SSL subject authentication.

• Supports using NFS, Alibaba Cloud OSS, AWS S3, and S3-compatible object storage services (such as Huawei OBS, Google GCS, and Tencent Cloud COS) as backup media.

• Supports automatic and manual cleanup of backups.

• Supports specifying backups or archives for cleanup.

• Supports backup and recovery at the tenant level.

• Supports recovery at the database and table levels.

• Supports recovery to a specified path.

• Supports log archiving and verification of backup data.

• Supports query rewriting.

• Supports prepared statements.

• Supports the cost-based optimizer.

• Supports execution plan generation and display (EXPLAIN).

• Supports execution plan caching.

• Supports rapid parameterization of execution plans.

• Supports execution plan binding.

• Supports Optimizer Hint.

• Supports adaptive cursor sharing (ACS).

• Supports execution plan management (SPM).