OceanBase Database Subscription

Product Version: V4.3.5 2OceanBase Database Declaration controlled by OceanBase agreements, and this document is not part of, nor does it modify, any agreement between OceanBase and its customers. > Product Version: V4.3.5 2OceanBase Database Preface Preface Overview This document template is the OceanBase Word style template. OceanBase documents and materials must be output according to this template style. Scope of Application Documents and materials related to OceanBase. Template Updates If there are any missing styles in the template, please contact Meina Wang (Yuhuang) to update the template. Get Updates Please obtain the template updates from the following address: > Product Version：V4.3.5 3OceanBase Database What is OceanBase Database 目录 1 Overview 10 1.1 Core features 10 1.1.1 High availability 10 1.1.2 High compatibility 10 1.1.3 Scaling out 10 1.1.4 Low costs 10 1.1.5 Real-time HTAP 11 1.1.6 Security and reliability 11 1.2 Learn more about OceanBase Database 11 2 Differences between Enterprise Edition and Community Edition 12 2.0.0.1 Note 12 2.0.0.2 Note 14 3 System architecture 18 3.1 Shared-nothing architecture 18 3.1.0.1 Applicability 20 3.2 Shared-storage architecture 20 > product version：V4.3.5 5OceanBase Database What is OceanBase Database 4 Overview 22 4.1 SQL data types 22 4.2 Built-in functions 23 4.3 SQL syntaxes 23 4.3.0.1 Note 24 4.4 PL features 25 4.5 System views 26 4.6 Character sets 26 4.6.0.1 Note 27 4.7 Collations 27 4.7.0.1 Note 29 4.7.0.2 Note 29 4.7.0.3 Note 29 4.7.0.4 Note 30 4.7.0.5 Note 30 4.7.0.6 Note 30 4.7.0.7 Note 30 4.7.0.8 Note 31 4.7.0.9 Note 31 4.7.0.10 Note 31 4.7.0.11 Note 31 4.7.0.12 Note 32 4.7.0.13 Note 32 4.7.0.14 Note 32 4.7.0.15 Note 32 4.7.0.16 Note 33 4.7.0.17 Note 33 4.7.0.18 Note 33 4.8 Database object management 33 4.9 Security features 35 4.10 Backup and restore 37 4.11 SQL engine 37 5 SQL data types 39 > product version：V4.3.5 6OceanBase Database What is OceanBase Database 6 Built-in functions 41 6.1 Numeric functions 42 6.2 String functions 43 6.3 Date functions 44 6.4 General comparison functions 45 6.5 Conversion functions 45 6.6 Encoding and decoding functions 47 6.7 Environment and identifier functions 47 6.8 Hierarchical functions 47 6.9 NULL-related functions 48 6.10 JSON functions 48 6.11 XML functions 49 6.12 Aggregate functions 50 6.12.0.1 Note 52 6.13 Analytic functions 52 6.13.0.1 Note 52 7 System views 53 7.1 Dictionary views 53 7.2 Performance views 61 > product version：V4.3.5 7OceanBase Database What is OceanBase Database 8 Compatibility with MySQL 62 8.1 Data types 62 8.1.0.1 Note 63 8.2 SQL syntaxes 63 8.3 PL features 64 8.4 System views 64 8.5 Character sets 64 8.5.0.1 Note 65 8.5.0.2 Note 65 8.6 Collations 65 8.6.0.1 Note 68 8.6.0.2 Note 68 8.6.0.3 Note 68 8.6.0.4 Note 69 8.6.0.5 Note 69 8.6.0.6 Note 69 8.6.0.7 Note 69 8.6.0.8 Note 70 8.6.0.9 Note 70 8.6.0.10 Note 70 8.6.0.11 Note 70 8.6.0.12 Note 71 8.6.0.13 Note 71 8.6.0.14 Note 71 8.6.0.15 Note 71 8.6.0.16 Note 72 8.6.0.17 Note 72 8.6.0.18 Note 72 8.7 Functions 72 8.8 Partition support 73 8.8.0.1 Notice 73 8.9 Backup and restore 73 8.10 Storage engine 73 8.11 Optimizer 74 > product version：V4.3.5 8OceanBase Database What is OceanBase Database 9 Limitations 76 9.1 Cluster name length 76 9.2 Identifier length 76 9.3 ODP connections 77 9.3.1 Connections to databases 77 9.3.2 Maximum number of connections 77 9.3.2.1 Note 77 9.4 Maximum number of partition replicas 77 9.4.0.1 Note 77 9.5 Single table 78 9.5.0.1 Note 78 9.6 Single column 78 9.7 String type 78 9.8 Feature usage 79 9.9 Shared storage 79 9.9.1 Physical standby databases 80 > product version：V4.3.5 9OceanBase Database What is OceanBase Database 1 Overview OceanBase Database is an enterprise-level, native distributed database independently developed by the OceanBase team. It provides financial-grade high availability on commodity hardware. Its groundbreaking deployment mode of "five IDCs across three regions" sets a new standard for automatic, lossless disaster recovery at the region level. OceanBase Database has also set new records in the TPC-C benchmark test, supporting more than 1,500 nodes in one cluster. It is cloud- native, highly consistent, and highly compatible with Oracle and MySQL. 1.1 Core features 1.1.1 High availability OceanBase Database implements a unique disaster recovery solution with five IDCs across three regions, which sets forth a new standard for lossless disaster recovery in the financial industry. OceanBase Database supports multi-active IDCs deployed across multiple regions for zone- and geo-disaster recovery, which meets the Level 6 disaster recovery requirements of the financial industry with a recovery point objective (RPO) of 0 and a recovery time objective (RTO) of less than 8 seconds. 1.1.2 High compatibility OceanBase Database is highly compatible with most general features of Oracle and MySQL, and supports advanced features such as procedural language and triggers. OceanBase Migration Service (OMS), an automatic migration tool, is provided to support migration assessment and reverse synchronization to ensure data migration security when a core system is migrated to OceanBase Database in key industries such as finance, public governance, and communication service. 1.1.3 Scaling out OceanBase Database supports rapid transparent horizontal scaling in response to business fluctuations, and achieves high performance based on a quasi-memory transaction processing architecture. You can deploy thousands of nodes in an OceanBase cluster, where the maximum data volume can exceed 3 PB and a single table can contain trillions of rows. 1.1.4 Low costs OceanBase Database adopts a storage engine based on the log-structured merge- tree (LSM-tree), which can achieve a high compression ratio and reduce storage > product version：V4.3.5 10OceanBase Database What is OceanBase Database costs by 70% to 90%. OceanBase Database also supports the multi-tenant architecture, which means that the same cluster can serve multiple business lines with the data of one tenant isolated from that of others. These features reduce deployment and O&M costs. 1.1.5 Real-time HTAP OceanBase Database uses the same database engine to perform online real-time transactions and real-time analysis on the same set of data. Multiple replicas of the same set of data can be stored in different forms for different purposes. This fundamentally ensures data consistency. 1.1.6 Security and reliability The OceanBase team has been independently developing OceanBase Database since 2010 and has full control over its source code. The integrated architecture of OceanBase Database has reliably supported large-scale financial core systems for many years. In addition to a comprehensive role and permission management system, OceanBase Database also provides transparent encryption of data for storage and communication, supports ShangMi (SM) encryption algorithms, and is certified to meet the standards of Multi-Level Protection Scheme (MLPS) Level 3. 1.2 Learn more about OceanBase Database You can learn more about OceanBase Database by reading the following topics: HA deployment solutions for OceanBase clusters System architecture OceanBase concepts > product version：V4.3.5 11OceanBase Database What is OceanBase Database 2 Differences between Enterprise Edition and Community Edition OceanBase Database is available in two forms: OceanBase Database Enterprise Edition and OceanBase Database Community Edition. OceanBase Database Enterprise Edition is an enterprise-level, native distributed database independently developed by the OceanBase team. It provides financial- grade high availability on commodity hardware. Its groundbreaking deployment mode of "five IDCs across three regions" sets a new standard for automatic, lossless disaster recovery at the region level. OceanBase Database has also set new records in the TPC-C benchmark test, supporting more than 1,500 nodes in one cluster. It is cloud-native, highly consistent, and highly compatible with Oracle and MySQL. OceanBase Database Community Edition is an integrated database that supports both standalone and distributed deployment modes. It is built on the native distributed architecture and supports enterprise-level features such as financial- grade high availability, transparent horizontal scaling, distributed transactions, multitenancy, and syntax compatibility. OceanBase Database Community Edition is open-source and compatible with MySQL. It not only supports connectivity with virtualization and big data applications and products, but also supports a comprehensive range of GUI-based development tools, O&M and monitoring tools, and data migration tools. In addition, it provides open APIs and various eco- capabilities to meet the business requirements of enterprises or individuals. 2.0.0.1 Note In this topic, a distributed transaction is an internal transaction within an OceanBase cluster. A distributed transaction is supported only in one tenant although the data that the transaction accesses involves multiple participants. eXtended Architecture (XA) ensures the atomicity of committing cross-database transactions. If a global transaction of an application accesses data in multiple databases, an external distributed transaction manager that is independent from OceanBase Database can call an XA transaction interface to ensure the atomicity of committing the global transaction. This way, the global transaction can also be seen as an XA transaction. The following table describes the features supported by these two editions. Category Feature Enterprise Edition Community Edition > product version：V4.3.5 12OceanBase Database What is OceanBase Database Product architecture Storage and compute Supported Not supported separation Core components Integrated SQL engine Supported Supported Core components I n t e g r a t e d Supported Supported transaction engine Core components Integrated storage Supported Supported engine Core components Cluster scheduling Supported Supported service Core components Cluster proxy service Supported Supported Core components Client, C driver, and Supported Supported Java driver High availability Multi-replica support Supported Supported High availability Five IDCs across three Supported Supported regions High availability T r a n s p a r e n t Supported Supported horizontal scaling High availability M u l t i - t e n a n t Supported Supported management High availability Tenant cloning Supported Supported High availability Data backup and Supported Supported restore High availability Resource isolation Supported Supported High availability Physical standby Supported Supported database High availability Arbitration service Supported Not supported Compatibility MySQL syntax and Supported Supported protocols Compatibility Data types and Supported Supported functions Compatibility Stored procedures Supported Supported and packages Compatibility Complex character Supported Supported sets Compatibility Oracle syntax Supported Not supported Compatibility XA transactions Supported Supported > product version：V4.3.5 13OceanBase Database What is OceanBase Database Supported 2.0.0.1 Compatibility LOCK TABLE Supported Note In V4.3.5, this feature is supported starting from V4.3.5 BP2. Compatibility F u n c t i o n - b a s e d Supported Supported indexes High performance Cost-based optimizer Supported Supported Optimization and High performance rewriting of complex Supported Supported queries High performance Parallel execution Supported Supported engine High performance Vectorized engine Supported Supported High performance Columnar engine Supported Supported High performance Advanced SQL plan Supported Not supported management (SPM) High performance M i n i m u m Supported Supported specifications High performance Paxos-based log Supported Supported transmission Distributed strong- c o n s i s t e n c y High performance t r a n s a c t i o n s , Supported Supported complete ACID, and multi-version support Data partitioning High performance (RANGE, HASH, and Supported Supported LIST) High performance Partition exchange Supported Supported High performance Partition splitting Supported Supported High performance Global indexes Supported Supported High performance Multi-valued indexes Supported Supported High performance Full-text indexes Supported Supported > product version：V4.3.5 14OceanBase Database What is OceanBase Database High performance A d v a n c e d Supported Supported compression High performance Dynamic sampling Supported Supported High performance Auto degree of Supported Supported parallelism (DOP) High performance Materialized views Supported Supported Cross-data source Read-only external Supported Supported access tables Cross-data source DBLink Supported Supported access Multimodel OBKV-Table Supported Supported Multimodel OBKV-HBase Supported Supported Multimodel JSON Supported Supported G e o g r a p h i c Multimodel information system Supported Supported (GIS) Multimodel XML Supported Supported Multimodel Vector Supported Supported Security Audit Supported Not supported Security Privilege management Supported Supported Security C o m m u n i c a t i o n Supported Supported encryption Not supported. OceanBase Database Advanced security Community Edition Security scaling Supported does not support transparent data encryption (TDE) or row-level labels. O&M management End-to-end tracing Supported Supported O&M components O&M management (liboblog and Supported Supported ob_admin) O&M management obloader and Supported Supported obdumper > product version：V4.3.5 15OceanBase Database What is OceanBase Database Supported. OceanBase Database Community Edition supports GUI-based development and management tools such as OceanBase G U I - b a s e d Cloud Platform (OCP), O&M management development and Supported OceanBase Migration management tools Service (OMS), and OceanBase Developer Center (ODC). You can download these tools for free. However, OceanBase Migration Assessment (OMA) is a paid service. OceanBase Database Community Edition provides only T e c h n i c a l c o m m u n i t y - b a s e d Support and services consultation (on Supported technical consultation products) on products. No commercial expert team is provided for technical consultation. OceanBase Database Community Edition provides online Service acquisition service consultation (channels for Commercial expert only on its official Support and services obtaining technical team website or in support) OceanBase Database Community and does not provide commercial expert teams. Expert services OceanBase Database ( p l a n n i n g , Community Edition Support and services i m p l e m e n t a t i o n , On-site services by does not provide inspection, fault commercial experts expert assurance recovery, and services. production assurance) > product version：V4.3.5 16OceanBase Database What is OceanBase Database OceanBase Database Community Edition Support and services Response to faults 24/7 services does not provide e m e r g e n c y t r o u b l e s h o o t i n g services. Cost efficiency Clog compression for Supported Not supported storage > product version：V4.3.5 17OceanBase Database What is OceanBase Database 3 System architecture OceanBase Database supports the shared-nothing (SN) architecture and the shared- storage (SS) architecture. 3.1 Shared-nothing architecture The distributed cluster architecture in SN mode is a common deployment method for OceanBase Database. In this mode, all nodes are equal, with each node equipped with its own SQL engine, storage engine, and transaction engine. This setup operates on a cluster of standard PC servers, delivering key advantages such as high scalability, availability, performance, cost-effectiveness, and strong compatibility with mainstream databases. An OceanBase cluster consists of multiple nodes. These nodes are distributed across several zones, with each node belonging to one zone. A zone is a logical concept that represents a group of nodes that share similar hardware availability in the cluster. Its meaning varies depending on the deployment mode. For example, in a scenario where the cluster is deployed in one IDC, nodes in a zone can belong to the same rack or the same switch. If the cluster is deployed across IDCs, each zone can correspond to one IDC. Each zone has two attributes: IDC and region. These > product version：V4.3.5 18OceanBase Database What is OceanBase Database attributes specify the IDC in which the zone is located and the region to which the IDC belongs. Typically, the region refers to the city where the IDC is located. The IDC and region attributes of a zone should reflect the actual deployment circumstances to ensure the proper functioning of automatic disaster recovery mechanisms and optimization strategies within the cluster. To meet different high availability requirements of business for database systems, OceanBase Database offers various deployment modes. For more information, see HA deployment solutions for OceanBase clusters. In OceanBase Database, data in a table can be horizontally divided into multiple shards based on a specified division rule. Each shard is known as a table partition or simply a partition. A row of data belongs to and only exists in one partition. You can specify partitioning rules when you create a table. Partitions of the hash, range, and list types, and subpartitions are supported. For example, you can divide an order table in a transaction database into several partitions by user ID, and then divide each partition into several subpartitions by month. In a subpartition table, each subpartition is a physical partition, while a partition is merely a logical concept. Multiple partitions of a table can be distributed across multiple nodes within a zone. Each physical partition has a storage layer object, called a tablet, for storing ordered data records. When you modify data in a tablet, the system records redo logs to the corresponding log stream (LS) to ensure data persistence. Each log stream serves multiple tablets on the local node. To protect data and ensure service continuity when a node fails, each log stream and tablet have multiple replicas. Typically, multiple replicas of a log stream or tablet are distributed across zones. You can modify only one of the replicas, which is referred to as the leader. Other replicas are referred to as followers. Data consistency is ensured between the leader and followers based on the Multi-Paxos protocol. If the node where the leader is located is down, a follower is elected as the new leader to continue to provide services. Each node in the cluster runs an observer process, which contains multiple operating system threads. All nodes provide the same features. Each observer process accesses data of partitions on the node in which it runs, and parses and executes SQL statements routed to the node. The observer processes on the nodes communicate with each other by using the TCP/IP protocol. In addition, each service listens to connection requests from external applications, establishes connections and database sessions, and provides database services. For more information about observer processes, see Threads. OceanBase Database provides the unique multitenancy feature to simplify the management of multiple business databases deployed on a large scale and reduce resource costs. In an OceanBase cluster, you can create multiple isolated "instances" of databases, which are referred to as tenants. From the perspective of applications, each tenant is a separate database. In addition, you can create a tenant in MySQL- > product version：V4.3.5 19OceanBase Database What is OceanBase Database or Oracle-compatible mode. After your application is connected to a MySQL- compatible tenant, you can create users and databases in the tenant. The user experience is similar to that with a standalone MySQL database. In the same way, after your application is connected to an Oracle-compatible tenant, you can create schemas and manage roles in the tenant. The user experience is similar to that with a standalone Oracle database. After a new cluster is initialized, a tenant named sys is created. The sys tenant is a MySQL-compatible tenant that stores the metadata of the cluster. 3.1.0.1 Applicability OceanBase Database Community Edition provides only the MySQL mode. To isolate the resources of different tenants, each observer process can have multiple virtual containers known as resource units that belong to different tenants. The resource units of each tenant across multiple nodes form a resource pool. A resource unit includes CPU and memory resources. To shield applications from internal details such as the distribution of partitions and replicas in OceanBase Database and make accessing a distributed database as simple as accessing a standalone database, we provide the OceanBase Database Proxy (ODP) service (also known as OBProxy). An application does not directly connect to an OBServer node. Instead, it connects to ODP. Then, ODP forwards SQL requests to an appropriate OBServer node. ODP is a stateless service, and multiple ODP nodes provide a unified network address to the application through network load balancing (SLB). 3.2 Shared-storage architecture To provide users with more cost-effective database services in multi-cloud environments, OceanBase Database implements the shared-storage (SS) architecture based on general-purpose object storage. This enables cloud-native database services on the cloud, reducing database usage costs while improving performance and usability. > product version：V4.3.5 20OceanBase Database What is OceanBase Database The SS mode adopts a storage-compute separation architecture. Each tenant stores one copy of data and logs on shared object storage, while caching hot data and logs on local storage of the node. Log replication is implemented between log stream replicas through Paxos. All replicas first write logs to the local storage of the nodes, and each leader uploads a complete set of logs to the object storage. Each secondary replica automatically identifies the log''s access frequency, caching only a small amount of hot logs locally. Each leader replica uploads a complete set of baseline data to the object storage. The replicas share the baseline data in the object storage, and all replicas automatically identify data access frequency, caching only hot data locally. Each replica independently performs minor compaction. Minor compaction data is not shared between replicas. > product version：V4.3.5 21OceanBase Database What is OceanBase Database 4 Overview This topic describes the compatibility between OceanBase Database''s Oracle mode and native Oracle Database. OceanBase Database is compatible with Oracle Database in terms of basic features, including the data types, SQL features, and database objects. OceanBase Database is compatible with most Procedural Language (PL)-related R&D features of Oracle Database. In terms of advanced features such as database security, backup and restore, high availability, and optimizer, OceanBase Database provides sound compatibility with and even outperforms Oracle Database. Therefore, you can smoothly migrate data and services from Oracle Database to OceanBase Database without spending much time learning everything about OceanBase Database. However, due to differences in underlying architecture and product structure, there are some features that OceanBase Database does not support or where it behaves differently from Oracle Database. This topic covers the compatibility between OceanBase Database''s Oracle mode and Oracle Database in the following areas: SQL data types Built-in functions SQL syntaxes PL features System views Character sets Collations Database object management Security features Backup and restore SQL engine 4.1 SQL data types OceanBase Database is compatible with most of the data types supported by Oracle Database. For more information, see SQL data types. However, for optimization reasons, the LONG and LONG RAW data types are considered too outdated, and OceanBase Database currently has no plans to support these two data types. > product version：V4.3.5 22OceanBase Database What is OceanBase Database 4.2 Built-in functions OceanBase Database is compatible with most of the built-in functions supported by Oracle Database. For more information, see Built-in functions. 4.3 SQL syntaxes OceanBase Database supports most of the SQL syntaxes in Oracle Database. For some unsupported SQL syntaxes, OceanBase Database will return an "unsupported syntax" error to indicate that the corresponding features are unavailable. Supported Not supported 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 > product version：V4.3.5 23OceanBase Database What is 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. 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. 4.3.0.1 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. Materialized views Does not support the ON COMMIT or ON STATMENT refresh mode in the CREATE MATERIALIZED VIEW statement. Does not support the PRESERVE TABLE clause in the DROP MATERIALIZED VIEW statement. Materialized view logs Does not support the following items in the syntax of the CREATE MATERIALIZED VIEW LOG statement: The ASYNCHRONOUS clause for asynchronous purging. > product version：V4.3.5 24OceanBase Database What is OceanBase Database The EXCLUDING NEW VALUES clause. If it is used, OceanBase Database returns an error. Partitions. 4.4 PL features OceanBase Database is compatible with most PL features of Oracle Database. For more information, see PL reference. Supported Not supported 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. > product version：V4.3.5 25OceanBase Database What is OceanBase 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. OceanBase Database does not support the following PL feature of Oracle Database: Conditional compilation 4.5 System views OceanBase Database is compatible with some system views of Oracle Database. For more information, see System views. For more information about columns in system views, see Overview of system views. 4.6 Character sets The Oracle mode of OceanBase Database supports the following character sets: binary utf8mb4 gbk utf16 gb18030 latin1 gb18030_2022 ascii tis620 utf16le sjis hkscs hkscs31 dec8 gb2312 ujis euckr eucjpms cp932 cp850 > product version：V4.3.5 26OceanBase Database What is OceanBase Database hp8 macroman swe7 4.6.0.1 Note For OceanBase Database V4.3.5, the following character sets are supported starting from V4.3.5 BP1: gb2312 , ujis , euckr , eucjpms , cp932 , cp850 , hp8 , macroman , and swe7 . 4.7 Collations The following table describes the collations supported by the Oracle mode of OceanBase Database. Collation Character set Description utf8mb4_general_ci utf8mb4 Uses general collation. utf8mb4_bin utf8mb4 Uses binary collation. Uses collation based on the utf8mb4_unicode_ci utf8mb4 Unicode Collation Algorithm (UCA). binary binary Uses binary collation. gbk_chinese_ci gbk Uses Chinese language collation. gbk_bin gbk Uses binary collation. utf16_general_ci utf16 Uses general collation. utf16_bin utf16 Uses binary collation. utf16_unicode_ci utf16 Uses collation based on the UCA. gb18030_chinese_ci gb18030 Uses Chinese language collation. gb18030_bin gb18030 Uses binary collation. latin1_swedish_ci latin1 Uses Swedish/Finnish collation. Uses collation for the German latin1_german1_ci latin1 language environment in the latin1 character set. > product version：V4.3.5 27OceanBase Database What is OceanBase Database Uses collation for the Danish latin1_danish_ci latin1 language environment in the latin1 character set. Used for German environments, suitable for latin1_german2_ci latin1 applications needing dictionary order character comparison. Used for case-insensitive scenarios supporting latin1_general_ci latin1 diacritics, such as database designs for some European languages. Used for case-sensitive general collation, supporting latin1_general_cs latin1 multiple languages (for example, Western European languages). Used for collation in the latin1_spanish_ci latin1 Spanish language environment. latin1_bin latin1 Uses binary collation for latin1. Uses binary collation, which is gb18030_2022_bin gb18030_2022 the default character sequence in Oracle mode. gb18030_2022_chinese_ci gb18030_2022 Uses pinyin collation, which is case-insensitive. gb18030_2022_chinese_cs gb18030_2022 Uses pinyin collation, which is case-sensitive. gb18030_2022_radical_ci gb18030_2022 Uses radical-stroke collation, which is case-insensitive. gb18030_2022_radical_cs gb18030_2022 Uses radical-stroke collation, which is case-sensitive. gb18030_2022_stroke_ci gb18030_2022 Uses stroke collation, which is case-insensitive. gb18030_2022_stroke_cs gb18030_2022 Uses stroke collation, which is case-sensitive. ascii_bin ascii Uses binary collation. > product version：V4.3.5 28OceanBase Database What is OceanBase Database Uses case-insensitive ascii_general_ci ascii alphabetical collation, treating uppercase and lowercase letters as the same. tis620_bin tis620 Uses binary collation. tis620_thai_ci tis620 Uses Thai collation, which is case-insensitive. Uses GB2312 character set, performs case-insensitive sorting according to Chinese collation. gb2312_chinese_ci gb2312 4.7.0.1 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses GB2312 character set, performs case-sensitive sorting in binary order. gb2312_bin gb2312 4.7.0.2 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses UJIS character set, performs case-insensitive sorting according to Japanese collation. ujis_japanese_ci ujis 4.7.0.3 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 29OceanBase Database What is OceanBase Database Uses UJIS character set, performs case-sensitive sorting in binary order. ujis_bin ujis 4.7.0.4 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses EUCKR character set, performs case-insensitive sorting according to Korean collation. euckr_korean_ci euckr 4.7.0.5 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses EUCKR character set, performs case-sensitive sorting in binary order. euckr_bin euckr 4.7.0.6 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses EUCJPMS character set, performs case-insensitive sorting according to Japanese collation. eucjpms_japanese_ci eucjpms 4.7.0.7 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 30OceanBase Database What is OceanBase Database Uses EUCJPMS character set, performs case-sensitive sorting in binary order. eucjpms_bin eucjpms 4.7.0.8 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses CP932 character set, performs case-insensitive sorting according to Japanese collation. cp932_japanese_ci cp932 4.7.0.9 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses CP932 character set, performs case-sensitive sorting in binary order. cp932_bin cp932 4.7.0.10 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses CP850 character set, performs case-insensitive sorting according to general collation. cp850_general_ci cp850 4.7.0.11 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 31OceanBase Database What is OceanBase Database Uses CP850 character set, performs case-sensitive sorting in binary order. cp850_bin cp850 4.7.0.12 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses HP8 character set, performs case-insensitive sorting according to English collation. hp8_english_ci hp8 4.7.0.13 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses HP8 character set, performs case-sensitive sorting in binary order. hp8_bin hp8 4.7.0.14 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses MacRoman character set, performs case-insensitive sorting according to general collation. macroman_general_ci macroman 4.7.0.15 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 32OceanBase Database What is OceanBase Database Uses MacRoman character set, performs case-sensitive sorting in binary order. macroman_bin macroman 4.7.0.16 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses SWE7 character set, performs case-insensitive sorting according to Swedish collation. swe7_swedish_ci swe7 4.7.0.17 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses SWE7 character set, performs case-sensitive sorting in binary order. swe7_bin swe7 4.7.0.18 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. 4.8 Database object management Supported Not supported 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. > product version：V4.3.5 33OceanBase Database What is OceanBase Database 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. > product version：V4.3.5 34OceanBase Database What is OceanBase Database 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. Constraints Does not support DISABLE operations for the UNIQUE constraint. Partition support Does not allow you to add or drop subpartitions for template-based subpartitioned tables. Index management Does not support index types such as bitmap indexes or reverse indexes. Updatable views Does not support the WITH CHECK OPTION clause. 4.9 Security features OceanBase Database implements a wide range of security features. Supported Not supported 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. > product version：V4.3.5 35OceanBase Database What is OceanBase Database 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. Auditing Does not support unified auditing or fine-grained auditing (FGA). Does not support privilege auditing or network auditing. Does not allow you to delete audit data. Label security > product version：V4.3.5 36OceanBase Database What is OceanBase Database Does not support the compartment component or group component of Oracle Label Security. 4.10 Backup and restore OceanBase Database also supports backup and restore features. For more information about the physical backup and restore features of OceanBase Database, see Overview of backup and restore. Supported Not supported 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. Does not support backup and restore at the cluster level. Does not support backup at the table level. Does not support compression for log archives. Does not support backup to a specified path. Does not support manual cleanup of backups. Does not support secondary backup. Does not support the verification of backup data. 4.11 SQL engine The SQL engine of OceanBase Database is compatible with most features of Oracle Database. For more information about the SQL engine of OceanBase Database, see SQL tuning. Supported Not supported Supports query rewriting. Supports precompiled statements. Supports the cost-based optimizer. Supports the EXPLAIN feature for execution plan generation and display. > product version：V4.3.5 37OceanBase Database What is OceanBase Database 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. Does not support the estimator feature. Does not support execution plan isolation. Does not support expression statistics store (ESS). Does not support approximate query processing. > product version：V4.3.5 38OceanBase Database What is OceanBase Database 5 SQL data types This topic describes the compatibility of OceanBase Database''s Oracle mode with native Oracle Database in terms of SQL data types. Oracle Database supports 24 data types, and OceanBase Database supports 20 of them. The following table lists the data types. No. Oracle data type Supported in OceanBase Database? > product version：V4.3.5 39OceanBase Database What is OceanBase Database 1 CHAR (size) Yes 2 NCHAR[(size)] Yes 3 VARCHAR2(size) Yes 4 VARCHAR(size) Yes 5 NVARCHAR2(size) Yes 6 NUMBER [ (p [, s]) ] Yes 7 FLOAT [(p)] Yes 8 BINARY_FLOAT Yes 9 BINARY_DOUBLE Yes 10 LONG (deprecated in Oracle No Database) 11 DATE Yes 12 TIMESTAMP [(fractional_secon Yes ds_precision)] TIMESTAMP [(fractional_secon 13 ds_precision)] WITH TIME Yes ZONE TIMESTAMP [(fractional_secon 14 ds_precision)] WITH LOCAL Yes TIME ZONE 15 I N T E R V A L Y E A R Yes [(year_precision)] TO MONTH I N T E R V A L D A Y 16 [(day_precision)] TO SECOND [( Yes fractional_seconds_precision)] 17 RAW(size) Yes 18 LONG RAW (deprecated in No Oracle Database) 19 ROWID Yes 20 UROWID [(size)] Yes 21 BFILE No 22 BLOB Yes 23 CLOB Yes 24 NCLOB No > product version：V4.3.5 40OceanBase Database What is OceanBase Database 6 Built-in functions This topic describes the compatibility of OceanBase Database''s Oracle mode with native Oracle Database in terms of built-in functions. Oracle Database (19c) supports 312 built-in functions and OceanBase Database supports 173 of them. For more information, see Functions. > product version：V4.3.5 41OceanBase Database What is OceanBase Database 6.1 Numeric functions No. Oracle function Supported in OceanBase Database? 1 ABS Yes 2 ACOS Yes 3 ASIN Yes 4 ATAN Yes 5 ATAN2 Yes 6 BITAND Yes 7 CEIL Yes 8 COS Yes 9 COSH Yes 10 EXP Yes 11 FLOOR Yes 12 LN Yes 13 LOG Yes 14 MOD Yes 15 NANVL Yes 16 POWER Yes 17 REMAINDER Yes 18 ROUND (number) Yes 19 SIGN Yes 20 SIN Yes 21 SINH Yes 22 SQRT Yes 23 TAN Yes 24 TANH Yes 25 TRUNC (number) Yes 26 WIDTH_BUCKET Yes > product version：V4.3.5 42OceanBase Database What is OceanBase Database 6.2 String functions No. Oracle function Supported in OceanBase Database? 1 CHR Yes 2 CONCAT Yes 3 INITCAP Yes 4 LOWER Yes 5 LPAD Yes 6 LTRIM Yes 7 NLS_LOWER Yes 8 NLS_UPPER Yes 9 REGEXP_REPLACE Yes 10 REGEXP_SUBSTR Yes 11 REPLACE Yes 12 RPAD Yes 13 RTRIM Yes 14 SUBSTR Yes 15 TRANSLATE Yes 16 TRANSLATE ... USING Yes 17 TRIM Yes 18 UPPER Yes 19 ASCII Yes 20 INSTR Yes 21 LENGTH Yes 22 REGEXP_COUNT Yes 23 REGEXP_INSTR Yes > product version：V4.3.5 43OceanBase Database What is OceanBase Database 6.3 Date functions No. Oracle function Supported in OceanBase Database? 1 ADD_MONTHS Yes 2 CURRENT_DATE Yes 3 CURRENT_TIMESTAMP Yes 4 DBTIMEZONE Yes 5 EXTRACT (datetime) Yes 6 FROM_TZ Yes 7 LAST_DAY Yes 8 LOCALTIMESTAMP Yes 9 MONTHS_BETWEEN Yes 10 NEXT_DAY Yes 11 NUMTODSINTERVAL Yes 12 NUMTOYMINTERVAL Yes 13 ROUND (date) Yes 14 SESSIONTIMEZONE Yes 15 SYS_EXTRACT_UTC Yes 16 SYSDATE Yes 17 SYSTIMESTAMP Yes 18 TO_CHAR (datetime) Yes 19 TO_DSINTERVAL Yes 20 TO_TIMESTAMP Yes 21 TO_TIMESTAMP_TZ Yes 22 TO_YMINTERVAL Yes 23 TRUNC (date) Yes 24 TZ_OFFSET Yes > product version：V4.3.5 44OceanBase Database What is OceanBase Database 6.4 General comparison functions No. Oracle function Supported in OceanBase Database? 1 GREATEST Yes 2 LEAST Yes 6.5 Conversion functions No. Oracle function Supported in OceanBase Database? > product version：V4.3.5 45OceanBase Database What is OceanBase Database 1 ASCIISTR Yes 2 CAST Yes 3 CHARTOROWID Yes 4 CONVERT Yes 5 HEXTORAW Yes 6 NUMTODSINTERVAL Yes 7 NUMTOYMINTERVAL Yes 8 RAWTOHEX Yes 9 ROWIDTOCHAR Yes 10 ROWIDTONCHAR Yes 11 SCN_TO_TIMESTAMP Yes 12 TIMESTAMP_TO_SCN Yes 13 TO_BINARY_DOUBLE Yes 14 TO_BINARY_FLOAT Yes 15 TO_BLOB Yes 16 TO_CHAR (character) Yes 17 TO_CHAR (datetime) Yes 18 TO_CHAR (number) Yes 19 TO_CLOB Yes 20 TO_DATE Yes 21 TO_DSINTERVAL Yes 22 TO_MULTI_BYTE Yes 23 TO_NUMBER Yes 24 TO_NCHAR (character) Yes 25 TO_NCHAR (datetime) Yes 26 TO_NCHAR (number) Yes 27 TO_SINGLE_BYTE Yes 28 TO_TIMESTAMP Yes 29 TO_TIMESTAMP_TZ Yes 30 TO_YMINTERVAL Yes 31 UNISTR Yes > product version：V4.3.5 46OceanBase Database What is OceanBase Database 6.6 Encoding and decoding functions No. Oracle function Supported in OceanBase Database? 1 DECODE Yes 2 DUMP Yes 3 ORA_HASH Yes 4 VSIZE Yes 6.7 Environment and identifier functions No. Oracle function Supported in OceanBase Database? 1 SYS_CONTEXT Yes 2 SYS_GUID Yes 3 UID Yes 4 USER Yes 5 USERENV Yes 6.8 Hierarchical functions No. Oracle function Supported in OceanBase Database? 1 SYS_CONNECT_BY_PATH Yes > product version：V4.3.5 47OceanBase Database What is OceanBase Database 6.9 NULL-related functions No. Oracle function Supported in OceanBase Database? 1 COALESCE Yes 2 LNNVL Yes 3 NANVL Yes 4 NULLIF Yes 5 NVL Yes 6 NVL2 Yes 6.10 JSON functions No. Oracle function Supported in OceanBase Database? 1 JSON_ARRAY Yes 2 JSON_OBJECT Yes 3 JSON_ARRAYAGG Yes 4 JSON_OBJECTAGG Yes 5 JSON_VALUE Yes 6 JSON_QUERY Yes 7 JSON_MERGEPATCH Yes 8 JSON_EQUAL Yes 9 JSON_TABLE Yes > product version：V4.3.5 48OceanBase Database What is OceanBase Database 6.11 XML functions No. Oracle function Supported in OceanBase Database? 1 XMLAGG Yes 2 XMLELEMENT Yes 3 XMLPARSE Yes 4 EXTRACT Yes 5 EXTRACTVALUE Yes 6 XMLSERIALIZE Yes 7 XMLCAST Yes 8 UPDATEXML Yes > product version：V4.3.5 49OceanBase Database What is OceanBase Database 6.12 Aggregate functions > product version：V4.3.5 50OceanBase Database What is OceanBase Database No. Oracle function Supported in OceanBase Database? 1 APPROX_COUNT_DISTINCT Yes 2 AVG * Yes 3 CORR * Yes 4 COUNT * Yes 5 COVAR_POP * Yes 6 COVAR_SAMP * Yes 7 CUME_DIST * Yes 8 DENSE_RANK * Yes 9 GROUPING Yes 10 KEEP * Yes 11 LISTAGG * Yes 12 MAX * Yes 13 MIN * Yes 14 MEDIAN * Yes 15 PERCENT_RANK * Yes 16 PERCENTILE_CONT * Yes 17 PERCENTILE_DISC * Yes 18 RANK * Yes 19 REGR_ (Linear Regression) * Yes 20 ROLLUP Yes 21 STDDEV * Yes 22 STDDEV_POP * Yes 23 STDDEV_SAMP * Yes 24 SUM * Yes 25 VAR_POP * Yes 26 VAR_SAMP * Yes 27 VARIANCE * Yes 28 W M S Y S . W M _ C O N C A T Yes /WM_CONCAT * > product version：V4.3.5 51OceanBase Database What is OceanBase Database 6.12.0.1 Note A function followed by an asterisk (*) in the preceding table can be used as both an aggregate function and an analytic function. 6.13 Analytic functions No. Oracle function Supported in OceanBase Database? 1 FIRST_VALUE Yes 2 LAG Yes 3 LAST_VALUE Yes 4 LEAD Yes 5 NTH_VALUE Yes 6 NTILE Yes 7 RATIO_TO_REPORT Yes 8 ROW_NUMBER Yes 6.13.0.1 Note Analytic functions that can also be used as aggregate functions are listed in the preceding Aggregate functions table and are marked with an asterisk (*). Therefore, these functions are not listed in this table. > product version：V4.3.5 52OceanBase Database What is OceanBase Database 7 System views This topic describes the compatibility of OceanBase Database''s Oracle mode with native Oracle Database in terms of dictionary views and dynamic performance views. 7.1 Dictionary views OceanBase Database is compatible with the following dictionary views in Oracle Database: ALL_TAB_PARTITIONS ALL_ARGUMENTS ALL_TAB_SUBPARTITIONS ALL_COLL_TYPES ALL_COL_PRIVS ALL_CONSTRAINTS ALL_CONS_COLUMNS ALL_DEF_AUDIT_OPTS ALL_ERRORS ALL_INDEXES ALL_IND_COLUMNS ALL_IND_EXPRESSIONS ALL_IND_PARTITIONS ALL_IND_SUBPARTITIONS ALL_METHOD_PARAMS ALL_OBJECTS DBA_TAB_PARTITIONS ALL_PART_KEY_COLUMNS DBA_TAB_SUBPARTITIONS ALL_PROCEDURES ALL_SCHEDULER_JOB_ARGS ALL_SCHEDULER_PROGRAM_ARGS ALL_SEQUENCES ALL_SOURCE > product version：V4.3.5 53OceanBase Database What is OceanBase Database USER_TAB_SUBPARTITIONS ALL_SUBPART_KEY_COLUMNS ALL_SYNONYMS ALL_TABLES ALL_TAB_COLS ALL_TAB_COLUMNS USER_TAB_PARTITIONS ALL_ALL_TABLES ALL_TAB_PRIVS ALL_COL_COMMENTS ALL_TRIGGERS ALL_TYPES ALL_TYPE_ATTRS ALL_TYPE_METHODS ALL_USERS ALL_VIEWS ALL_DIRECTORIES ALL_PART_COL_STATISTICS ALL_PART_HISTOGRAMS ALL_SUBPART_COL_STATISTICS ALL_SUBPART_HISTOGRAMS ALL_TAB_COL_STATISTICS ALL_TAB_HISTOGRAMS ALL_TAB_STATISTICS AUDIT_ACTIONS ALL_PART_INDEXES ALL_DEPENDENCIES ALL_TAB_STATS_HISTORY DBA_TAB_STATS_HISTORY USER_TAB_STATS_HISTORY DBA_ARGUMENTS > product version：V4.3.5 54OceanBase Database What is OceanBase Database DBA_AUDIT_EXISTS DBA_AUDIT_OBJECT DBA_AUDIT_SESSION DBA_AUDIT_STATEMENT DBA_AUDIT_TRAIL DBA_COLL_TYPES ALL_PART_TABLES DBA_COL_PRIVS DBA_CONSTRAINTS DBA_CONS_COLUMNS DBA_ERRORS DBA_INDEXES DBA_IND_COLUMNS DBA_IND_EXPRESSIONS DBA_IND_PARTITIONS DBA_IND_SUBPARTITIONS DBA_METHOD_PARAMS DBA_OBJECTS DBA_OBJ_AUDIT_OPTS ALL_SUBPARTITION_TEMPLATES DBA_PART_KEY_COLUMNS ALL_TAB_COMMENTS DBA_PROCEDURES DBA_PROFILES DBA_ROLES DBA_ROLE_PRIVS DBA_SCHEDULER_JOB_ARGS DBA_SCHEDULER_PROGRAM_ARGS DBA_SEGMENTS DBA_SEQUENCES DBA_SOURCE > product version：V4.3.5 55OceanBase Database What is OceanBase Database DBA_STMT_AUDIT_OPTS DBA_ALL_TABLES DBA_SUBPART_KEY_COLUMNS DBA_SYNONYMS DBA_SYS_PRIVS DBA_TABLES DBA_TABLESPACES DBA_TAB_COLS DBA_TAB_COLUMNS DBA_COL_COMMENTS DBA_PART_INDEXES DBA_TAB_PRIVS DBA_PART_TABLES DBA_TRIGGERS DBA_TYPES DBA_TYPE_ATTRS DBA_TYPE_METHODS DBA_USERS DBA_VIEWS DBA_DEPENDENCIES NLS_DATABASE_PARAMETERS NLS_INSTANCE_PARAMETERS NLS_SESSION_PARAMETERS STMT_AUDIT_OPTION_MAP DBA_SUBPARTITION_TEMPLATES DBA_RECYCLEBIN DBA_RSRC_CONSUMER_GROUPS DBA_RSRC_GROUP_MAPPINGS DBA_RSRC_PLANS DBA_RSRC_PLAN_DIRECTIVES DBA_DIRECTORIES > product version：V4.3.5 56OceanBase Database What is OceanBase Database DBA_JOBS DBA_JOBS_RUNNING DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS DBA_TAB_COL_STATISTICS DBA_TAB_HISTOGRAMS DBA_TAB_STATISTICS USER_ARGUMENTS USER_AUDIT_OBJECT USER_AUDIT_SESSION USER_AUDIT_STATEMENT USER_AUDIT_TRAIL USER_COLL_TYPES DBA_TAB_COMMENTS USER_COL_PRIVS USER_CONSTRAINTS USER_CONS_COLUMNS USER_ERRORS USER_INDEXES USER_IND_COLUMNS USER_IND_EXPRESSIONS USER_IND_PARTITIONS USER_IND_SUBPARTITIONS USER_OBJECTS USER_ALL_TABLES USER_PART_KEY_COLUMNS USER_COL_COMMENTS USER_PROCEDURES USER_ROLE_PRIVS > product version：V4.3.5 57OceanBase Database What is OceanBase Database USER_SCHEDULER_JOB_ARGS USER_SCHEDULER_PROGRAM_ARGS USER_SEGMENTS USER_SEQUENCES USER_SOURCE USER_PART_INDEXES USER_SUBPART_KEY_COLUMNS USER_SYNONYMS USER_SYS_PRIVS USER_PART_TABLES USER_TRIGGERS USER_TABLES USER_TABLESPACES USER_TAB_COLS USER_TAB_COLUMNS USER_SUBPARTITION_TEMPLATES USER_TAB_COMMENTS USER_TYPES USER_TYPE_ATTRS USER_TYPE_METHODS USER_VIEWS USER_RECYCLEBIN USER_DEPENDENCIES USER_JOBS USER_PART_COL_STATISTICS USER_PART_HISTOGRAMS USER_SUBPART_COL_STATISTICS USER_SUBPART_HISTOGRAMS USER_TAB_COL_STATISTICS USER_TAB_HISTOGRAMS USER_TAB_STATISTICS > product version：V4.3.5 58OceanBase Database What is OceanBase Database ROLE_TAB_PRIVS ROLE_SYS_PRIVS ROLE_ROLE_PRIVS DICTIONARY DICT DBA_SQL_PLAN_BASELINES ALL_IND_STATISTICS ALL_IND_STATISTICS DBA_IND_STATISTICS USER_IND_STATISTICS ALL_TAB_MODIFICATIONS DBA_TAB_MODIFICATIONS USER_TAB_MODIFICATIONS USER_TAB_PRIVS USER_PROFILES DBA_SCHEDULER_JOBS DBA_SCHEDULER_PROGRAM DBA_CONTEXT DBA_POLICIES ALL_POLICIES USER_POLICIES DBA_POLICY_GROUPS ALL_POLICY_GROUPS USER_POLICY_GROUPS DBA_POLICY_CONTEXTS ALL_POLICY_CONTEXTS USER_POLICY_CONTEXTS DBA_SEC_RELEVANT_COLS ALL_SEC_RELEVANT_COLS USER_SEC_RELEVANT_COLS ALL_TRIGGER_ORDERING > product version：V4.3.5 59OceanBase Database What is OceanBase Database DBA_TRIGGER_ORDERING USER_TRIGGER_ORDERING ALL_DB_LINKS DBA_DB_LINKS USER_DB_LINKS DBA_SCHEDULER_JOB_RUN_DETAILS DBA_WR_SNAPSHOT DBA_WR_ACTIVE_SESSION_HISTORY DBA_WR_STATNAME DBA_WR_SYSSTAT DBA_WR_CONTROL DBMS_LOCK_ALLOCATED USER_USERS DBA_WR_SYSTEM_EVENT DBA_WR_EVENT_NAME DBA_WR_SQLSTAT DBA_WR_SYS_TIME_MODEL DBA_WR_SQLTEXT ALL_MVIEW_LOGS DBA_MVIEW_LOGS USER_MVIEW_LOGS DBA_MVIEWS ALL_MVIEWS USER_MVIEWS DBA_MVREF_STATS_SYS_DEFAULTS USER_MVREF_STATS_SYS_DEFAULTS DBA_MVREF_STATS_PARAMS USER_MVREF_STATS_PARAMS DBA_MVREF_RUN_STATS USER_MVREF_RUN_STATS DBA_MVREF_STATS > product version：V4.3.5 60OceanBase Database What is OceanBase Database USER_MVREF_STATS DBA_MVREF_CHANGE_STATS USER_MVREF_CHANGE_STATS DBA_MVREF_STMT_STATS USER_MVREF_STMT_STATS DBA_INDEX_USAGE 7.2 Performance views OceanBase Database is compatible with the following dynamic performance views in Oracle Database: V$NLS_PARAMETERS V$DBLINK V$INSTANCE V$SESSION_WAIT V$SESSION_WAIT_HISTORY V$SESSTAT V$SYSTEM_EVENT V$VERSION V$SQL_MONITOR_STATNAME V$GLOBAL_TRANSACTION V$TIMEZONE_NAMES V$ENCRYPTED_TABLESPACES V$RSRC_PLAN V$SYSSTAT V$EVENT_NAME V$ACTIVE_SESSION_HISTORY V$DML_STATS V$OPEN_CURSOR V$SQL_JOIN_FILTER V$STATNAME V$OB_PL_CACHE_OBJECT > product version：V4.3.5 61OceanBase Database What is OceanBase Database 8 Compatibility with MySQL This topic describes the compatibility between OceanBase Database''s MySQL mode and native MySQL Database. The MySQL mode of OceanBase Database is compatible with most features and statements of MySQL 5.7 and 8.0. Some features are not supported because of their limited popularity or the product architecture differences between the two databases. This topic will cover the following aspects of compatibility between OceanBase Database and MySQL Database: Data types SQL syntaxes Procedural Language (PL) features System views Character sets Collations Functions and expressions Partition support Backup and restore Storage engine Optimizer 8.1 Data types OceanBase Database supports the following data types: Numeric data types Integer types: BOOL / BOOLEAN / TINYINT , SMALLINT , MEDIUMINT , INT / INTEGER , and BIGINT Fixed-point types: DECIMAL and NUMERIC Floating-point types: FLOAT and DOUBLE Bit-value type: BIT Date and time types: DATETIME , TIMESTAMP , DATE , TIME , and YEAR Character types: CHAR , VARCHAR , BINARY , and VARBINARY > product version：V4.3.5 62OceanBase Database What is OceanBase Database Large object types: TINYBLOB , BLOB , MEDIUMBLOB , and LONGBLOB Text types: TINYTEXT , TEXT , MEDIUMTEXT , LONGTEXT , and STRING 8.1.0.1 Note For OceanBase Database V4.3.5, the text data type STRING is supported starting from V4.3.5 BP1. Enumeration type: ENUM Set type: SET JSON data types Spatial data types Roaring Bitmap data types Array data types Vector data types 8.2 SQL syntaxes Supported Not supported SELECT Supports most query features, including single- and multi-table queries, subqueries, inner joins, semi joins, outer joins, grouping, aggregation, and regular data mining functions such as probability and linear regression functions. Allows you to perform set operations such as UNION , UNION ALL , MINUS , EXCEPT , and INTERSECT on the results of multiple SELECT queries. Supports the EXPLAIN statement for viewing execution plans. INSERT Supports single- and multi-row inserts. OceanBase Database also supports inserting data into a specified partition. Supports the INSERT INTO ... SELECT ... statement. UPDATE Supports single- and multi-column updates. Supports updates by using subqueries. > product version：V4.3.5 63OceanBase Database What is OceanBase Database Supports set updates. DELETE Supports single- and multi-table deletion. TRUNCATE Allows you to truncate a specified table. SELECT Does not support the SELECT ... FOR SHARE ... statement. TRUNCATE Does not support truncating a table that has a transaction in progress or a table that is being locked. 8.3 PL features OceanBase Database is compatible with most PL features of MySQL Database, including: Data types Stored procedures Custom functions Triggers Exception handling OceanBase Database also supports specific MySQL PL packages, such as DBMS_RESOURCE_MANAGER , DBMS_STATS , DBMS_UDR , DBMS_XPLAN , and DBMS_WORKLOAD_REPOSITORY . For more information about PL features, see PL reference. 8.4 System views OceanBase Database implements most views in the information_schema and mysql databases of MySQL Database. However, due to architectural differences, OceanBase Database does not implement all views in MySQL Database or ensure consistency with MySQL Database in all column meanings of the views. For more information about system views, see Overview of system views. 8.5 Character sets OceanBase Database supports the following character sets: > product version：V4.3.5 64OceanBase Database What is OceanBase Database binary utf8mb4/utf8mb3 8.5.0.1 Note utf8mb3 is an alias of utf8mb4 . gbk utf16 utf16le gb18030 latin1 gb18030_2022 ascii tis620 sjis big5 dec8 gb2312 ujis euckr eucjpms cp932 cp850 hp8 macroman swe7 8.5.0.2 Note For OceanBase Database V4.3.5, the following character sets are supported starting from V4.3.5 BP1: gb2312 , ujis , euckr , eucjpms , cp932 , cp850 , hp8 , macroman , and swe7 . 8.6 Collations > product version：V4.3.5 65OceanBase Database What is OceanBase Database The following collations are supported in OceanBase Database. Collation Character set Description utf8mb4_general_ci utf8mb4 Uses general collation. utf8mb4_bin utf8mb4 Uses binary collation. Uses collation based on the utf8mb4_unicode_ci utf8mb4 Unicode Collation Algorithm (UCA). Uses the collation rules of utf8mb4_unicode_520_ci utf8mb4 Unicode V5.2.0. It follows Unicode code point sorting and ignores case differences. Uses Croatian collation rules. utf8mb4_croatian_ci is utf8mb4_croatian_ci utf8mb4 compatible with utf8_croatian_ci. utf8mb4_czech_ci utf8mb4 Uses Czech collation rules. Uses Unicode version 9.0.0 collation rules and order, utf8mb4_0900_ai_ci utf8mb4 ignoring case differences and treating uppercase and lowercase letters as the same character. binary binary Uses binary collation. gbk_chinese_ci gbk Uses Chinese language collation. gbk_bin gbk Uses binary collation. utf16_general_ci utf16 Uses general collation. utf16_bin utf16 Uses binary collation. utf16_unicode_ci utf16 Uses collation based on the UCA. utf8mb4_german2_ci utf16le Uses German collation rules. utf8mb4_croatian_ci utf16le Uses Croatian collation rules. gb18030_chinese_ci gb18030 Uses Chinese language collation. gb18030_bin gb18030 Uses binary collation. > product version：V4.3.5 66OceanBase Database What is OceanBase Database latin1_swedish_ci latin1 Uses Swedish/Finnish collation. Uses collation for the German latin1_german1_ci latin1 language environment in the latin1 character set. Uses collation for the Danish latin1_danish_ci latin1 language environment in the latin1 character set. Used for German environments, suitable for latin1_german2_ci latin1 applications needing dictionary order character comparison. Used for case-insensitive scenarios supporting latin1_general_ci latin1 diacritics, such as database designs for some European languages. Used for case-sensitive general collation, supporting latin1_general_cs latin1 multiple languages (for example, Western European languages). Used for collation in the latin1_spanish_ci latin1 Spanish language environment. latin1_bin latin1 Uses binary collation for latin1. gb18030_2022_bin gb18030_2022 Uses binary collation. gb18030_2022_chinese_ci gb18030_2022 Uses pinyin collation, which is case-insensitive. gb18030_2022_chinese_cs gb18030_2022 Uses pinyin collation, which is case-sensitive. gb18030_2022_radical_ci gb18030_2022 Uses radical-stroke collation, which is case-insensitive. gb18030_2022_radical_cs gb18030_2022 Uses radical-stroke collation, which is case-sensitive. gb18030_2022_stroke_ci gb18030_2022 Uses stroke collation, which is case-insensitive. > product version：V4.3.5 67OceanBase Database What is OceanBase Database gb18030_2022_stroke_cs gb18030_2022 Uses stroke collation, which is case-sensitive. ascii_bin ascii Uses binary collation. Uses case-insensitive ascii_general_ci ascii alphabetical collation, treating uppercase and lowercase letters as the same. tis620_bin tis620 Uses binary collation. tis620_thai_ci tis620 Uses Thai collation, which is case-insensitive. sjis_japanese_ci sjis Uses Japanese collation rules. dec8_swedish_ci dec8 Uses Swedish collation rules. Uses GB2312 character set, performs case-insensitive sorting according to Chinese collation. gb2312_chinese_ci gb2312 8.6.0.1 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses GB2312 character set, performs case-sensitive sorting in binary order. gb2312_bin gb2312 8.6.0.2 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses UJIS character set, performs case-insensitive sorting according to Japanese collation. ujis_japanese_ci ujis 8.6.0.3 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 68OceanBase Database What is OceanBase Database Uses UJIS character set, performs case-sensitive sorting in binary order. ujis_bin ujis 8.6.0.4 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses EUCKR character set, performs case-insensitive sorting according to Korean collation. euckr_korean_ci euckr 8.6.0.5 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses EUCKR character set, performs case-sensitive sorting in binary order. euckr_bin euckr 8.6.0.6 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses EUCJPMS character set, performs case-insensitive sorting according to Japanese collation. eucjpms_japanese_ci eucjpms 8.6.0.7 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 69OceanBase Database What is OceanBase Database Uses EUCJPMS character set, performs case-sensitive sorting in binary order. eucjpms_bin eucjpms 8.6.0.8 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses CP932 character set, performs case-insensitive sorting according to Japanese collation. cp932_japanese_ci cp932 8.6.0.9 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses CP932 character set, performs case-sensitive sorting in binary order. cp932_bin cp932 8.6.0.10 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses CP850 character set, performs case-insensitive sorting according to general collation. cp850_general_ci cp850 8.6.0.11 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 70OceanBase Database What is OceanBase Database Uses CP850 character set, performs case-sensitive sorting in binary order. cp850_bin cp850 8.6.0.12 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses HP8 character set, performs case-insensitive sorting according to English collation. hp8_english_ci hp8 8.6.0.13 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses HP8 character set, performs case-sensitive sorting in binary order. hp8_bin hp8 8.6.0.14 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses MacRoman character set, performs case-insensitive sorting according to general collation. macroman_general_ci macroman 8.6.0.15 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. > product version：V4.3.5 71OceanBase Database What is OceanBase Database Uses MacRoman character set, performs case-sensitive sorting in binary order. macroman_bin macroman 8.6.0.16 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses SWE7 character set, performs case-insensitive sorting according to Swedish collation. swe7_swedish_ci swe7 8.6.0.17 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. Uses SWE7 character set, performs case-sensitive sorting in binary order. swe7_bin swe7 8.6.0.18 Note For OceanBase Database V4. 3.5, this collation is supported since V4.3.5 BP1. 8.7 Functions Supported Not supported The analytic (window) functions supported by OceanBase Database are a superset of those supported by MySQL Database. This means that OceanBase Database supports all analytic (window) functions supported by MySQL Database. The MySQL mode of OceanBase Database does not support the following functions: String functions: LOAD_FILE() and MATCH() XML functions: ExtractValue() and UpdateXML() Other functions: MASTER_POS_WAIT() > product version：V4.3.5 72OceanBase Database What is OceanBase Database 8.8 Partition support The partition support feature of OceanBase Database differs from that of MySQL Database in the following aspects: OceanBase Database supports partitioning, template-based subpartitioning, and non-template-based subpartitioning. MySQL Database does not support non- template-based subpartitioning. OceanBase Database supports HASH, KEY, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS subpartitioning. MySQL Database supports only HASH and KEY subpartitioning. OceanBase Database supports adding and dropping subpartitions in partitioned tables. MySQL Database does not support adding and dropping subpartitions. 8.8.0.1 Notice Adding and dropping subpartitions is not supported for scenarios where the subpartition type is HASH or KEY. In OceanBase Database V4.3.5, support for adding subpartitions is available starting from V4.3.5 BP1. For more information about partitions, see Partition overview. 8.9 Backup and restore OceanBase Database is compatible with some backup and restore features of MySQL Database. Supported Not supported Full backup and incremental backup Hot backup Table-level and database-level restore Restore to a specified path Cluster-level backup and restore Cold backup Subpartition-level backup Verification of backup data 8.10 Storage engine > product version：V4.3.5 73OceanBase Database What is OceanBase Database Unlike the InnoDB and MyISAM storage engines in MySQL Database, the storage engine in OceanBase Database is based on the LSM-tree architecture. 8.11 Optimizer OceanBase Database is compatible with some optimizer features of MySQL Database. For more information about the optimizer, see SQL tuning. Supported Not supported Commands to view execution plans: The output columns of the execution plan view include only ID, OPERATOR, NAME , EST. ROWS, and COST, as well as the details of operators. Statistics queries: You can manually query the histogram statistics information stored in the data dictionary table by executing the ANALYZE TABLE statement. You can automatically view table statistics and column statistics through views. Query rewriting and optimization: Outer join optimization Outer join simplification Block Nested-Loop (BNL) and Batched Key Access (BKA) joins Conditional filtering Constant folding optimization IS NULL optimization (indexes do not store NULL values) ORDER BY optimization GROUP BY optimization Elimination by using DISTINCT LIMIT pushdown Window function optimization Avoiding full table scan Predicate pushdown Optimizer hint mechanism: Join-order optimizer hints > product version：V4.3.5 74OceanBase Database What is OceanBase Database Table-level optimizer hints Index-level optimizer hints INDEX , FULL , ORDERED , and LEADING hints Parallel execution capabilities such as parallel query, parallel replication, and parallel write, which are compatible with those in MySQL Database. OceanBase Database also supports parallel operations such as parallel aggregation, parallel join, parallel grouping, and parallel sorting. Plan caching and precompilation, which are not supported by MySQL Database. Commands to view execution plans: Does not support the SHOW WARNINGS statement for displaying additional information about execution plans. > product version：V4.3.5 75OceanBase Database What is OceanBase Database 9 Limitations 9.1 Cluster name length Item Maximum length Cluster name 128 bytes 9.2 Identifier length MySQL mode Oracle mode Item Maximum length Username 64 bytes Tenant name 63 bytes Database name 128 bytes Table name 64 characters Column name 128 bytes Index name 64 bytes View name 64 bytes Alias 255 bytes Table group name 127 bytes User-defined variable 64 characters Item Maximum length Username 64 bytes Table name 128 bytes Column name 128 bytes Index name 128 bytes View name 128 bytes Alias 128 bytes Object name 128 bytes Table group name 127 bytes > product version：V4.3.5 76OceanBase Database What is OceanBase Database 9.3 ODP connections Connections to databases When you connect to OceanBase Database V4.x by using OceanBase Database Proxy (ODP), free routing between primary and standby databases is not supported. Maximum number of connections Item Upper limit It is specified by the client_max_connections parameter of ODP. The default value is 8192. Note Number of connections per ODP You can increase the number of ODP nodes or the value of the client_max_connections parameter to increase the maximum number of connections for a cluster. 9.4 Maximum number of partition replicas Item Upper limit Unlimited. Note Number of partition replicas per OBServer node You can estimate the number of partition replicas of each OBServer node based on the memory size of the tenant. 1 GB of memory supports about 20,000 tablets. > product version：V4.3.5 77OceanBase Database What is OceanBase Database 9.5 Single table Item Upper limit Row length 1.5 MB Number of columns 4,096 Number of indexes 128 Total number of index columns 512 Index length 16 KB Total number of primary key columns 64 Primary key length 16 KB Oracle mode: 65,536 MySQL mode: 8,192–65,536 Note Number of partitions The maximum number of partitions in a single table in MySQL mode is specified by the tenant-level parameter max_partition_num . The default value is 8192 . 9.6 Single column Item Upper limit Length of an index column 16 KB 9.7 String type MySQL mode Oracle mode > product version：V4.3.5 78OceanBase Database What is OceanBase Database Item Maximum length CHAR 256 characters VARCHAR 262,144 characters BINARY 256 bytes VARBINARY 1,048,576 bytes TINYBLOB 255 bytes BLOB 65,535 bytes MEDIUMBLOB 16,777,215 bytes LONGBLOB 536,870,910 bytes TINYTEXT 255 bytes TEXT 65,535 bytes MEDIUMTEXT 16,777,215 bytes LONGTEXT 536,870,910 bytes Item Maximum length CHAR 2,000 bytes NCHAR 2,000 bytes VARCHAR 32,767 bytes VARCHAR2 32,767 bytes NVARCHAR2 32,767 bytes BLOB 536,870,910 bytes CLOB 536,870,910 bytes 9.8 Feature usage 9.9 Shared storage > product version：V4.3.5 79OceanBase Database What is OceanBase Database In the current version, the following limitations apply to OceanBase clusters with the shared-storage (SS) architecture: Multi-region deployment is not supported. The columnar storage engine is not supported. The physical standby database feature is not supported. The data_disk_size of a tenant cannot be reduced. At most 100,000 partitions are supported. Physical standby databases > product version：V4.3.5 80OceanBase Database What is OceanBase Database The following table describes the limitations on the physical standby database feature. Item Description Maximum number of standby tenants Unlimited. supported by one primary tenant Resources of the primary and standby tenants Whether homogeneous resources are do not need to be homogeneous. We required for the primary and standby tenants recommend that you use the same resource specifications for the primary and standby tenants. The parameters of the primary tenant are independent of those of a standby tenant, and parameter modifications are not Parameters physically synchronized. After you modify a parameter of the primary tenant, you must assess whether to modify the corresponding parameter of the standby tenants. System variables of the primary and standby tenants are physically synchronized. If you System variables modify a system variable of the primary tenant, the system synchronously modifies the corresponding system variable of the standby tenants. You can create users and change user Users and passwords passwords only in the primary tenant. The updated information is synchronized to the standby tenants. Read and write operations A standby tenant supports only read operations. Minor compactions in the primary tenant are independent of those in the standby tenants. Minor and major compactions Major compactions are not performed in a standby tenant. Instead, the major compaction information is synchronized from the primary tenant to the standby tenants. Switchover All replicas of log streams of the standby tenants must be online. Failover All replicas of log streams of the standby tenants must be online. > product version：V4.3.5 81">