Overview

SELECT

• Supports most query features, including single- and multi-table queries, subqueries, 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 collection 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 insertion into a specified partition.

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

• Supports single- and multi-table inserts.

UPDATE

• Supports single- and multi-column updates.

• Supports updates by using subqueries.

• Supports collection updates.

DELETE

• Supports single- and multi-table 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 about hints, see Hint.

Materialized views

• Supports the CREATE MATERIALIZED VIEW statement for creating a materialized view.
• Supports the DROP MATERIALIZED VIEW statement for dropping a materialized view.

Materialized view logs

• Supports the CREATE MATERIALIZED VIEW LOG statement for creating a materialized view log.

  Note

  The default behavior of with_clause (where you can specify PRIMARY KEY, ROWID, and SEQUENCE) in the CREATE MATERIALIZED VIEW LOG statement in OceanBase Database is incompatible with those in Oracle Database.

• Supports the DROP MATERIALIZED VIEW LOG statement for dropping a materialized view log.

OceanBase Database supports the following PL features of Oracle Database:

• Data types

• Process control

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

• Static SQL statements

• Dynamic SQL statements

• Subprograms

• 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

In the implementation of NOCOPY for a parameter, compatibility between OceanBase Database and Oracle Database varies as follows:

• 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: You can 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: 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 about the syntax for modifying tables, see ALTER TABLE.

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

Constraints

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

• Supports the PRIMARY KEY constraint.

• Supports the FOREIGN KEY constraint.

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

• Supports the SET NULL constraint 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, 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 Partition overview.

Index management

• Supports only B-tree indexes.

• Allows you to create and drop indexes.

View management

• Allows you to create simple and complex views.

• Allows you to drop views.

• Supports SELECT statements.

• Supports DML statements.

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 about the privileges supported by the Oracle mode of OceanBase Database, 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.

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

• 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 OBServer nodes and between OBServer nodes themselves.

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

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

• Supports automatic cleanup of expired backups.

• Supports backup and restore at the tenant level.

• Supports restore at the database and table levels.

• Supports restore to a specified path.

• Supports query rewriting.

• Supports precompiled statements.

• Supports the cost-based optimizer.

• Supports the EXPLAIN feature for execution plan generation and display.

• Supports the plan cache.

• Supports fast parameterization of execution plans.

• Supports execution plan binding.

• Supports optimizer hints.

• Supports adaptive cursor sharing (ACS).

• Supports the SQL plan management (SPM) feature.