Overview of Oracle compatibility

SELECT

• Supports most query features, including single-table and multi-table queries, subqueries, inner joins, semi-joins, outer joins, grouping, 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 the EXPLAIN syntax to view the execution plan.

INSERT

• Supports inserting single rows and multiple rows of data, and supports inserting data into specified partitions.

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

• Supports inserting data into single tables and multi-tables.

UPDATE

• Supports updating data in single columns and multiple columns.

• Supports updating data using subqueries.

• Supports set-based updates.

DELETE

• Supports deleting data from single tables and multi-tables.

TRUNCATE

• Supports truncating specified tables.

Parallel queries

• Supports parallel queries similar to those in Oracle Database.

  OceanBase Database supports the Auto DOP feature and also allows you to manually specify the DOP using Hint/Session variables.

• Supports parallel DML operations.

Hint

OceanBase Database supports the Hint syntax. For more information about Hints, see Hint.

Materialized views

• CREATE MATERIALIZED VIEW (create a materialized view).
• DROP MATERIALIZED VIEW (drop a materialized view).

Materialized view logs

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

  Note

  In OceanBase Database, the with_clause (such as PRIMARY KEY, ROWID, and SEQUENCE) of the CREATE MATERIALIZED VIEW LOG syntax has a different default behavior from that in Oracle Database.

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

OceanBase Database supports the following PL features:

• Data types

• Control flow

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

The behavior of the NOCOPY parameter in OceanBase Database is different from that in Oracle Database:

• For basic data types and complex data types with the OUT attribute, the reference passing behavior in Oracle Database is not supported.

• For complex data types with the IN OUT attribute, the reference passing behavior in Oracle Database is supported.

Table management

• Create tables: You can create tables and specify partitioning and constraints when you create a table. For more information about the syntax of the CREATE TABLE statement, 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 more information about the syntax of the ALTER TABLE statement, see ALTER TABLE.

• Drop base tables: You can drop a table and drop the constraints that are associated with the table. For more information about the syntax of the DROP TABLE statement, see DROP TABLE.

Constraints

• The CHECK, UNIQUE, and NOT NULL constraints are supported.

• The PRIMARY KEY constraint is supported.

• The FOREIGN KEY constraint is supported.

• You can use the ALTER TABLE statement to add the CHECK, PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints.

• The SET NULL option is supported in cascading operations.

Partitioning

• First-level partitioning, templated second-level partitioning, and non-templated second-level partitioning are supported.

• Hash, range, list, and composite partitioning are supported.

• Local and global indexes are supported.

• For partition maintenance operations:

  • You can add, drop, and truncate first-level partitions in a first-level partitioned table.

  • You can add and drop first-level partitions in a templated second-level partitioned table. You can add, drop, and truncate first-level partitions, and add, drop, and truncate second-level partitions in a non-templated second-level partitioned table.

For more information about partitions, see Partition overview.

Index management

• Only B-tree indexes, function indexes, and spatial indexes are supported in OceanBase Database.

• You can create and drop indexes.

View management

• You can create simple or complex views.

• You can drop views.

• You can use the SELECT statement.

• You can use DML statements.

Sequence management

• You can create sequences.

• You can modify sequences, including resetting the sequence value.

• You can drop sequences.

Synonyms

• You can create synonyms for tables, views, synonyms, and sequences.

• You can create public synonyms.

Trigger management

• You can create triggers.

• You can modify triggers.

• You can drop triggers.

Database links

• You can read from and write to OceanBase Database from OceanBase Database.

• You can read from and write to Oracle Database from OceanBase Database.

Privilege management

• Most system privileges of Oracle Database are supported. For more information about the privileges supported by OceanBase Database in Oracle mode, see Privilege classification in Oracle mode.

• Common object privilege management is supported, such as granting and revoking privileges.

• A whitelist strategy is supported to implement network security access control.

• System predefined roles and user-defined roles are supported.

User authentication

• The password policy of Oracle Database is supported in Oracle mode.

• User lock and unlock are supported.

Transparent data encryption

The transparent data encryption (TDE) feature of Oracle Database is supported. Data is automatically encrypted before being written to storage devices and automatically decrypted when read.

Audit

• Standard audit of Oracle Database is supported.

• Statement and object audit types are supported.

• Object audit is supported only for tables, sequences, functions, and packages.

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

• System views related to audit are supported.

Label security

• The label security feature of Oracle Database is supported.

• The Level dimension is supported.

Transport layer security (TLS) encryption

• TLS encryption is supported for data transmission between the client and OceanBase Database server, and between OceanBase Database nodes.

• In addition to supporting single-way SSL authentication and X.509 two-way authentication, special two-way authentication is supported, such as specifying the encryption algorithm, the issuer authentication, and the SSL subject authentication.

• You can use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Amazon Web Services (AWS) Simple Storage Service (S3), and object storage services that are compatible with the S3 protocol (such as Huawei Object Storage Service (OBS), Google Cloud Storage (GCS), and Tencent Cloud Object Storage (COS)) as backup media.

• You can automatically or manually clean up backups.

• You can clean up specified backups or archives.

• You can perform backup and recovery at the tenant level.

• You can perform database-level and table-level recovery.

• You can perform recovery by specifying a path.

• You can archive logs and verify backup data.

• Query rewriting is supported.

• Precompiled statements are supported.

• Cost-based optimizer is supported.

• Execution plan generation and display (EXPLAIN) are supported.

• Execution plan caching is supported.

• Fast parameterization of execution plans is supported.

• Execution plan binding is supported.

• Optimizer hints are supported.

• Adaptive cursor sharing (ACS) is supported.

• Execution plan management (SPM) is supported.