Compatibility with Oracle

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

• Supports updates by using subqueries.

• Supports set 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, 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.

  • Supports the DROP MATERIALIZED VIEW LOG statement for dropping 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.

OceanBase Database supports the following PL features of Oracle Database:

• 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 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, the behavior differs between the two databases.

• If the parameter has the IN OUT attribute and is of a complex data type, the two databases are compatible.

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, function-based indexes, and columnstore 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, 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 NFS, Alibaba Cloud OSS, Tencent Cloud COS, and AWS S3 as backup destinations.

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

• Supports plan binding.

• Supports optimizer hints.

• Supports adaptive cursor sharing (ACS).

• Supports the SQL plan management (SPM) feature.