Compatibility with Oracle

SELECT

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

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

• Supports the EXPLAIN statement for viewing execution plans.

INSERT

• Supports single- and multi-row inserts, as well as insert operations into a specified partition.

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

• Supports single- and multi-table inserts.

UPDATE

• Supports single- and multi-column data updates.

• Supports updates by using subqueries.

• Supports set updates.

DELETE

• Supports single- and multi-table data deletion.

TRUNCATE

• Supports the truncation of a specified table.

Parallel queries

• Supports parallel queries similar to those in Oracle Database.

  OceanBase Database supports the auto degree of parallelism (DOP) feature. You can also manually specify the DOP by using hints or session variables.

• Supports parallel DML statements.

Hint

OceanBase Database supports hints. For more information, see Hint.

OceanBase Database supports the following PL features of Oracle Database:

• Data types

• Process control

• Sets and records

• Static SQL statements

• Dynamic SQL statements

• Subprocedures

• Triggers

• Exception handling

• Packages

• Performance optimization

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

Table management

• Create tables: You can create tables and specify partitions and constraints when you create a table. For more information, see CREATE TABLE.

• Modify base tables: You can use the ALTER TABLE statement to add, drop, and modify columns; add, drop, and modify constraints; and add, drop, and modify partitions. For more information, see ALTER TABLE.

• Drop base tables: You can drop tables and the constraints on the tables. For more information, see DROP TABLE.

Constraints

• Supports CHECK, UNIQUE, and NOT NULL constraints.

• Supports PRIMARY KEY constraints.

• Supports FOREIGN KEY constraints.

• Allows you to add CHECK, PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints by executing the ALTER TABLE statement.

• Supports SET NULL for cascaded tables.

Partition support

• Supports partitioning, template-based subpartitioning, and non-template-based subpartitioning.

• Supports HASH, RANGE, LIST, and composite partitioning.

• Supports local and global indexes.

• For partition maintenance, take note of the following points:

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

For more information about partitions, see Partition overview.

Index management

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

• Allows you to create and drop indexes.

View management

• Allows you to create simple and complex views.

• Allows you to drop views.

• Allows you to query views using the SELECT statement.

• Allows you to execute DML statements on views.

Sequence management

• Allows you to create sequences.

• Allows you to modify sequences. For example, you can reset sequences.

• Allows you to drop sequences.

Synonym

• Allows you to create synonyms for objects such as tables, views, synonyms, and sequences.

• Allows you to create public synonyms.

Trigger management

• Allows you to create triggers.

• Allows you to modify triggers.

• Allows you to drop triggers.

DBLinks

• 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 in Oracle Database. For more information, see Privilege types in Oracle mode.

• Supports regular object privilege management operations such as privilege granting and revocation.

• Supports allowlist configuration for secure network access control.

• Supports predefined system roles and custom roles.

Identity authentication

• Compatible with the password strategies of Oracle Database.

• Supports user locking and unlocking.

TDE

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

• Supports the standard auditing feature of Oracle Database.

• Supports statement auditing and object auditing.

• Supports only tables, sequences, functions, and packages for object auditing.

• Allows you to store audit data in files or internal tables.

• Provides various audit-related system views.

Label security

• Compatible with the Oracle Label Security feature of Oracle Database.

• Supports the level component of Oracle Label Security.

SSL-based transmission link encryption

• Offers the ability to encrypt data transmission both between clients and OceanBase Database servers and between OceanBase Database nodes.

• Supports one-way Secure Sockets Layer (SSL) authentication, two-way X509 authentication, as well as additional special two-way authentication mechanisms.

• Allows you to use Network File System (NFS), Alibaba Cloud OSS, and Tencent Cloud COS as the backup destination.

• Supports automatic cleanup of expired backups.

• Supports backup and restore at the tenant level.

• Supports restore at the database and table levels.

• Supports query rewriting.

• Supports precompiled statements.

• Supports the cost-based optimizer.

• Supports the EXPLAIN feature for plan generation and display.

• Supports plan caching.

• Supports fast parameterization for plans.

• Supports plan binding.

• Supports optimizer hints.

• Supports adaptive cursor sharing (ACS).

• Supports the SQL plan management (SPM) feature.