Overview

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.

• OceanBase Database supports using the EXPLAIN syntax to view the execution plan.

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

TRUNCATE

• OceanBase Database allows you to truncate a specified table.

Parallel query

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

• OceanBase Database supports parallel DML statements.

Hints

OceanBase Database supports the use of hint syntax. For more information about hints, refer to Hint.

The main PL features supported by OceanBase Database are as follows:

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

• 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 has compatibility differences with Oracle Database in implementing the behavior of the NOCOPY parameter:

• If the parameter is of a basic data type or the parameter has the OUT attribute and is of a complex data type, OceanBase Database is incompatible with the pass-by-reference behavior in Oracle Database.

• If the parameter has the IN OUT attribute and is of a complex data type, OceanBase Database is compatible with the pass-by-reference behavior in Oracle Database.

Table management

• Create tables: OceanBase Database allows you to create tables and specify partitions and constraints when you create a table. For more information about the syntax for creating tables, see CREATE 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. For more information about the syntax for modifying tables, see ALTER TABLE.

• Drop base tables: OceanBase Database allows to you drop tables and the constraints on the tables. For more information about the syntax for dropping tables, see DROP TABLE.

Constraints

• OceanBase Database supports the CHECK, UNIQUE, and NOT NULL constraints.

• OceanBase Database supports the PRIMARY KEY constraint.

• OceanBase Database supports the FOREIGN KEY constraint.

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

• OceanBase Database supports the SET NULL constraint 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, note that:

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

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

• OceanBase Database supports only B-tree indexes.

• OceanBase Database allows you to create and drop indexes.

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.

Sequence management

• OceanBase Database allows you to create sequences.

• OceanBase Database allows you to modify sequences. For example, you can reset sequences.

• OceanBase Database allows you to drop sequences.

Synonyms

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

• OceanBase Database allows you to create public synonyms.

Trigger management

• OceanBase Database allows you to create triggers.

• OceanBase Database allows you to modify triggers.

• OceanBase Database allows you to drop triggers.

DBLinks

• OceanBase Database supports read and write operations on an OceanBase database by using a DBLink.

• OceanBase Database supports read and write operations on an Oracle database by using a DBLink.

Privilege management

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

• OceanBase Database supports regular object privilege management operations such as privilege granting and revocation.

• OceanBase Database supports allowlist configuration for secure network access control.

• OceanBase Database supports predefined system roles and custom roles.

Identity authentication

• The Oracle mode of OceanBase Database is compatible with the password strategies of Oracle Database.

• OceanBase Database supports user locking and unlocking.

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.

• OceanBase Database supports statement auditing and object auditing.

• OceanBase Database supports only tables, sequences, functions, and packages for object auditing.

• OceanBase Database allows to you store audit data in files or internal tables.

• OceanBase Database provides various audit-related system views.

Label security

• OceanBase Database is compatible with the Oracle Label Security feature of Oracle Database.

• OceanBase Database supports the level component of Oracle Label Security.

SSL-based transmission link encryption

• OceanBase Database offers the ability to encrypt data transmission both between clients and OBServer nodes and between OBServer nodes themselves.

• OceanBase Database supports one-way Secure Sockets Layer (SSL) authentication, two-way X509 authentication, as well as additional special two-way authentication mechanisms, such as specifying the encryption algorithm, issuer, and SSL subject for authentication.

• OceanBase Database allows you to use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Amazon Simple Storage Service (S3), and object storage services compatible with the S3 protocol as the backup media.

• OceanBase Database supports automatic cleanup of expired backups.

• OceanBase Database supports backup and restore at the tenant level.

• OceanBase Database supports restore at the database and table levels.

• OceanBase Database supports query rewriting.

• OceanBase Database supports precompiled statements.

• OceanBase Database supports the cost-based optimizer.

• OceanBase Database allows you to generate and display execution plans by using the EXPLAIN statement.

• OceanBase Database supports the plan cache.

• OceanBase Database supports fast parameterization of execution plans.

• OceanBase Database supports execution plan binding.

• OceanBase Database supports optimizer hints.

• OceanBase Database supports adaptive cursor sharing (ACS).

• OceanBase Database supports SQL plan management (SPM).