Database 23ai: Feature Highlights

Learn how Oracle Database 23ai brings AI to your data, making it simple to power app development and mission critical workloads with AI.

Each week, we'll share a new feature of Oracle Database 23ai with examples so you can get up and running quickly. Save this page and check back each week to see new highlighted features.

Oracle Database 23ai: AI for Data, AI for Devs, AI Where you Need It (2:33)
Najavljujemo Oracle Database 23ai: unesite umjetnu inteligenciju u svoje podatke

Larry Ellison i Juan Loaiza objašnjavaju strategiju GenAI na kojoj se temelji servis Oracle Database 23ai.

Oracle Database 23ai Feature highlights for developers

Check out some of the features we’ve built with developers in mind:

AI Vector Search brings AI to your data by letting you build generative AI pipelines using your business data, directly within the database. Easy-to-use native vector capabilities let your developers build next-gen AI applications that combine relational database processing with similarity search and retrieval augmented generation. Running vector search directly on your business data eliminates data movement as well as the complexity, cost, and data consistency headaches of managing and integrating multiple databases.

Other features developers should get to know include:

…and more!

  • Transparent Application Continuity shields C/C++, Java, .NET, Python, and Node.js applications from the outages of underlying software, hardware, communications, and storage layers...

  • If a transaction does not commit or rollback for a long time while holding row locks, it can potentially block other high-priority transactions...

  • DBMS_SEARCH implements Oracle Text ubiquitous search. DBMS_SEARCH makes it very easy to create a single index over multiple tables and views...

  • We've added enhancements to Memoptimized Rowstore Fast Ingest with support for partitioning, compressed tables, fast flush using direct writes, and direct in-memory column store population support...

  • Oracle Globally Distributed Database introduced the Raft replication feature in Oracle Database 23c. This allows us to achieve very fast (sub 3 seconds) failover with zero data loss in case of a node or a data center outage...

  • This week we’re turning the spotlight on SQL Analysis Report, an easy-to-use feature that helps developers write better SQL statements...

Application availability—zero downtime for database clients

Transparent Application Continuity shields C/C++, Java, .NET, Python, and Node.js applications from the outages of underlying software, hardware, communications, and storage layers. With Oracle Real Application Clusters (RAC), Active Data Guard (ADG), and Autonomous Database (Shared and Dedicated), Oracle Database remains accessible even when a node or a subset of the RAC cluster fails or is taken offline for maintenance.

Oracle Database 23c brings many new enhancements, including batch applications support, for example, open cursors, also called session state stable cursors.

Automatic Transaction Rollback

If a transaction does not commit or rollback for a long time while holding row locks, it can potentially block other high-priority transactions. This feature allows applications to assign priorities to transactions, and administrators to set timeouts for each priority. The database will automatically rollback a lower-priority transaction and release the row locks held if it blocks a higher-priority transaction beyond the set timeout, allowing the higher-priority transaction to proceed.

Automatic Transaction Rollback reduces the administrative burden while also helping to maintain transaction latencies/SLAs on higher-priority transactions.

Fast Ingest enhancements

We've added enhancements to Memoptimized Rowstore Fast Ingest with support for partitioning, compressed tables, fast flush using direct writes, and direct in-memory column store population support. These enhancements make the Fast Ingest feature easier to incorporate in more situations where fast data ingest is required. Now Oracle Database provides better support for applications requiring fast data ingest capabilities. Data can be ingested and then processed all in the same database. This reduces the need for special loading environments and thus reduces complexity and data redundancy.

Raft-based replication in Globally Distributed Database

Oracle Globally Distributed Database introduced the Raft replication feature in Oracle Database 23c. This allows us to achieve very fast (sub 3 seconds) failover with zero data loss in case of a node or a data center outage. Raft replication uses a consensus-based commit protocol and is configured declaratively by specifying the replication factor. All shards in a Distributed Database act as leaders and followers for a subset of data. This enables an active/active/active symmetric distributed database architecture where all shards serve application traffic.

This helps improve availability with zero data loss, simplify management, and optimize hardware utilization for Globally Distributed Database environments.

SQL Analysis Report

This week we’re turning the spotlight on SQL Analysis Report, an easy-to-use feature that helps developers write better SQL statements. SQL Analysis Report reports common issues with SQL statements, particularly those that can lead to poor SQL performance. It’s available in DBMS_XPLAN and SQL Monitor.

  • Blockchain and immutable tables, available since the release of Oracle Database 19c, use crypto-secure methods to help protect data from tampering or deletion by external hackers and rogue or compromised insiders...

  • Oracle Database now supports schema privileges in addition to existing object, system, and administrative privileges...

  • Use SQL Firewall to detect anomalies and prevent SQL injection attacks. SQL Firewall examines all SQL, including session context information such as IP address and OS user...

  • Oracle Database 23c includes the new role DB_DEVELOPER_ROLE, which provides an application developer with all the necessary privileges to design, implement, debug, and deploy applications on Oracle Databases...

Blockchain tables

Blockchain and immutable tables, available since the release of Oracle Database 19c, use crypto-secure methods to help protect data from tampering or deletion by external hackers and rogue or compromised insiders. This includes insert-only restrictions that prevent updates or deletions (even by DBAs), cryptographic hash chains to enable verification, signed table digests to detect any large-scale rollbacks, and end user signing of inserted rows using their private keys. Oracle Database 23c introduces many enhancements, including support for logical replication via Oracle GoldenGate and rolling upgrades using Active Data Guard, support for distributed transactions that involve blockchain tables, efficient partition-based bulk dropping for expired rows, and performance optimizations for inserts/commits.

This release also introduces the ability to add/drop columns without impacting cryptographic hash chaining, user-specific chains and table digests for filtered rows, delegate-signing capability, and database countersigning. It also expands crypto-secure data management to regular tables by enabling an audit of historical changes to a non-blockchain table via Flashback archive defined to use a blockchain history table.

Great for built-in audit trail or journaling use cases, these capabilities can be used for financial ledgers, payments history, regulated compliance tracking, legal logs, and any data representing assets where tampering or deletions could lead to significant legal, reputation, or financial consequences.

Schema privileges

Oracle Database now supports schema privileges in addition to existing object, system, and administrative privileges. This feature improves security by simplifying authorization for database objects to better implement the principle of least privilege and keep the guesswork out of who should have access to what.

SQL Firewall

Use SQL Firewall to detect anomalies and prevent SQL injection attacks. SQL Firewall examines all SQL, including session context information such as IP address and OS user. Embedded into the database kernel, SQL Firewall logs and (if enabled) blocks unauthorized SQL, ensuring that it can’t be bypassed. By enforcing an allow-list of SQL and approved session contexts, SQL Firewall can prevent many zero-day attacks and reduce the risk of credential theft or abuse.

DB_DEVELOPER_ROLE

Oracle Database 23c includes the new role DB_DEVELOPER_ROLE, which provides an application developer with all the necessary privileges to design, implement, debug, and deploy applications on Oracle Databases. By using this role, administrators no longer have to guess which privileges may be necessary for application development.

  • Oracle Database now supports the ISO SQL standard-compliant Boolean data type. This enables you to store True and False values in tables and use Boolean expressions in SQL statements...

  • Oracle Database now allows you to join the target table in UPDATE and DELETE statements to other tables using the FROM clause. These other tables can limit the rows that are changed or be the source of new values...

  • You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases...

  • DDL object creation, modification, and deletion in Oracle Database now supports the IF EXISTS and IF NOT EXISTS syntax modifiers...

  • Oracle Database 23c makes it easier for developers to calculate totals and averages over INTERVAL values...

  • The RETURNING INTO clause for INSERT, UPDATE, and DELETE statements has been enhanced to report old and new values affected by the respective statement...

  • You can now run SELECT expression-only queries without a FROM clause. This new feature improves SQL code portability and ease of use for developers.

  • Create SQL macros to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements...

  • PL/SQL functions within SQL statements are automatically converted (transpiled) into SQL expressions whenever possible...

  • The Oracle Database SQL engine now supports a VALUES clause for many types of statements...

  • Annotations enable you to store and retrieve metadata about database objects. They are free-form text fields applications can use to customize business logic or user interfaces...

  • Usage Domains (sometimes called SQL domains or Application Usage Domains) are high-level dictionary objects that act as lightweight type modifiers and centrally document intended data usage for applications...

  • Now you can store a larger number of attributes in a single row, which may simplify application design and implementation for some applications...

Boolean data type

Oracle Database now supports the ISO SQL standard-compliant Boolean data type. This enables you to store True and False values in tables and use Boolean expressions in SQL statements. The Boolean data type standardizes the storage of Yes and No values and makes it easier to migrate to Oracle Database.

Direct Joins for UPDATE and DELETE Statements

Oracle Database now allows you to join the target table in UPDATE and DELETE statements to other tables using the FROM clause. These other tables can limit the rows that are changed or be the source of new values. Direct joins make it easier to write SQL to change and delete data.

GROUP BY column alias

You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases. These new Database 23c enhancements make it easier to write GROUP BY and HAVING clauses, making SQL queries much more readable and maintainable while providing better SQL code portability.

IF [NOT] EXISTS

DDL object creation, modification, and deletion in Oracle Database now supports the IF EXISTS and IF NOT EXISTS syntax modifiers. This enables you to control whether an error should be raised if a given object exists or does not exist, simplifying error handling in scripts and by applications.

INTERVAL data type aggregations

Oracle Database 23c makes it easier for developers to calculate totals and averages over INTERVAL values. With this enhancement, you now can pass INTERVAL data types to the SUM and AVG aggregate and analytic functions.

RETURNING INTO clause

The RETURNING INTO clause for INSERT, UPDATE, and DELETE statements has been enhanced to report old and new values affected by the respective statement. This allows developers to use the same logic for each of these DML types to obtain values pre- and post-statement execution. Old and new values are valid only for UPDATE statements. INSERT statements don't report old values and DELETE statements don't report new values.

The ability to obtain old and new values affected by INSERT, UPDATE, and DELETE statements as part of the SQL command’s execution offers developers a uniform approach to reading these values and reduces the amount of work the database must perform.

SELECT without FROM clause

You can now run SELECT expression-only queries without a FROM clause. This new feature improves SQL code portability and ease of use for developers.

SQL Macros

Create SQL macros to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. SQL macros can be scalar expressions that are typically used in SELECT lists as well as WHERE, GROUP BY, and HAVING clauses. SQL macros can also be used to encapsulate calculations and business logic or can be table expressions, typically used in a FROM clause. Compared to PL/SQL constructs, SQL macros can improve performance. SQL macros increase developer productivity, simplify collaborative development, and improve code quality.

SQL Transpiler

PL/SQL functions within SQL statements are automatically converted (transpiled) into SQL expressions whenever possible. Transpiling PL/SQL functions into SQL statements can speed up overall execution time.

Table Value Constructor

The Oracle Database SQL engine now supports a VALUES clause for many types of statements. This enables you to materialize rows of data on the fly by specifying them using the new syntax without relying on existing tables. Oracle Database 23c supports the VALUES clause for the SELECT, INSERT, and MERGE statements. The introduction of the new VALUES clause allows developers to write less code for ad-hoc SQL commands, leading to better readability with less effort.

Usage Annotations

Annotations enable you to store and retrieve metadata about database objects. They are free-form text fields applications can use to customize business logic or user interfaces. Annotations are name-value pairs or simply a name. They help you use database objects in the same way across all applications, simplifying development and improving data quality.

Usage Domains

Usage Domains (sometimes called SQL domains or Application Usage Domains) are high-level dictionary objects that act as lightweight type modifiers and centrally document intended data usage for applications. Usage Domains can be used to define data usage and standardize operations to encapsulate a set of check constraints, display properties, ordering rules, and other usage properties—without requiring application-level meta data.

Usage Domains for one or more columns in a table do not modify the underlying data type and can, therefore, also be added to existing data without breaking applications or creating portability issues.

Wide tables—now 4,096 columns max

Now you can store a larger number of attributes in a single row, which may simplify application design and implementation for some applications.

The maximum number of columns allowed in a database table or view has been increased to 4,096. This feature goes beyond the previous 1,000-column limit, allowing you to build applications that can store attributes in a single table. Some applications such as machine learning and streaming Internet of Things (IoT) application workloads may require the use of de-normalized tables with more than 1,000 columns.

  • Oracle Database 23c and CMAN-TDM now bring best-in-class connection management and monitoring capabilities with implicit connection pooling, multi-pool DRCP, per-PDB PRCP, and much more...

  • With Oracle Database 23c, the Pipelining feature enables .NET, Java, and C/C++ applications to send multiple requests to the Database without waiting for the response from the server...

  • Multilingual engine (MLE) module calls allow developers to invoke JavaScript functions stored in modules from SQL and PL/SQL. Call specifications written in PL/SQL link JavaScript to PL/SQL code units...

  • A new feature of Oracle Database 23c is the client capability to store Oracle configuration information, such as connection strings, in Microsoft Azure App Configuration or Oracle Cloud Infrastructure Object Storage...

  • The three pillars of observability are metrics, logging, and distributed tracing. This release brings enhanced logging, new debugging (diagnose on first failure), and new tracing capabilities...

  • Oracle Database 23c introduces Transportable Binary XML (TBX), a new self-contained XMLType storage method. TBX supports sharding, XML search index, and Exadata pushdown operations, providing better performance and scalability than other XML storage options...

Connection management for extreme scalability

Oracle Database 23c and CMAN-TDM now bring best-in-class connection management and monitoring capabilities with implicit connection pooling, multi-pool DRCP, per-PDB PRCP, and much more. Enhance the scalability and power of your C, Java, Python, Node.js, and ODP.NET applications with the latest and greatest features in DRCP and PRCP. Monitor the usage of PRCP pool effectively with statistics from the new V$TDM_STATS dynamic view in Oracle Database 23c.

Database driver asynchronous programming and pipelining

With Oracle Database 23c, the Pipelining feature enables .NET, Java, and C/C++ applications to send multiple requests to the Database without waiting for the response from the server. Oracle Database queues and processes those requests one by one, allowing the client applications to continue working until notification of the completion of the requests. These enhancements provide a better end user experience, improved data-driven application responsiveness, end-to-end scalability, avoidance of performance bottlenecks, and efficient resource utilization on the server and the client sides.

For the client request to return immediately, Oracle Database Pipelining requires an asynchronous or reactive API in .NET, Java, and C/C++ drivers. These mechanisms can be used against Oracle Database, with or without Database Pipelining.

For Java, Oracle Database 23c furnishes the Reactive Extensions in Java Database Connectivity (JDBC), Universal Connection Pool (UCP), and the Oracle R2DBC Driver. It also supports the Java virtual threads in the driver (Project Loom) as well as the Reactive Streams libraries, such as Reactor, RxJava, Akka Streams, Vert.x, and more.

JavaScript stored procedures

Multilingual engine (MLE) module calls allow developers to invoke JavaScript functions stored in modules from SQL and PL/SQL. Call specifications written in PL/SQL link JavaScript to PL/SQL code units. This feature enables developers to use JavaScript functions anywhere PL/SQL functions are called.

Multicloud configuration and security integration

A new feature of Oracle Database 23c is the client capability to store Oracle configuration information, such as connection strings, in Microsoft Azure App Configuration or Oracle Cloud Infrastructure Object Storage. This new capability simplifies application cloud configuration, deployment, and connectivity with Oracle JDBC, .NET, Python, Node.js, and Oracle Call Interface data access drivers. The information is stored in configuration providers, which provides the benefit of separating application code and configuration.

Use with OAuth 2.0 single sign-on to the cloud and database to further enhance the ease of administration. Oracle Database 23c clients can use Microsoft Entra ID, Azure Active Directory, or Oracle Cloud Infrastructure access tokens for database sign-on.

Observability, OpenTelemetry, and diagnosability for Java and .NET applications

The three pillars of observability are metrics, logging, and distributed tracing. This release brings enhanced logging, new debugging (diagnose on first failure), and new tracing capabilities. The JDBC and ODP.NET drivers have also been instrumented with a hook for tracing database calls; this hook enables distributed tracing using OpenTelemetry.

Transportable Binary XML

Oracle Database 23c introduces Transportable Binary XML (TBX), a new self-contained XMLType storage method. TBX supports sharding, XML search index, and Exadata pushdown operations, providing better performance and scalability than other XML storage options.

With the support of more database architectures, such as sharding or Exadata, and its capability to easily migrate and exchange XML data among different servers, containers, and PDBs, TBX allows your applications to take full advantage of this new XML storage format on more platforms and architectures.

You can migrate existing XMLType storage of a different format to TBX format in any of the following ways:

Insert-as select or create-as-select

Online redefinition

Oracle Data Pump

  • The JSON data type is an Oracle-optimized binary JSON format called OSON. It is designed for faster query and DML performance in the database and in database clients from release 21c and on...

  • JSON Relational Duality, an innovation introduced in Oracle Database 23c, unifies the relational and document data models to provide the best of both worlds...

  • Oracle Database supports JSON to store and process schema-flexible data. With Oracle Database 23c, Oracle Database now supports JSON Schema to validate structure and values of JSON data...

  • The PL/SQL JSON constructor is enhanced to accept an instance of a corresponding PL/SQL aggregate type, returning a JSON object or array type populated with the aggregate type data.

  • With the Oracle Database API for MongoDB, developers can continue to use MongoDB's tools and drivers connected to an Oracle Database while gaining access to Oracle's multimodel capabilities and self-driving database...

JSON binary data type

The JSON data type is an Oracle-optimized binary JSON format called OSON. It is designed for faster query and DML performance in the database and in database clients from release 21c and on.

JSON Relational Duality views

JSON Relational Duality, an innovation introduced in Oracle Database 23c, unifies the relational and document data models to provide the best of both worlds. Developers can build applications in either relational or JSON paradigms with a single source of truth and benefit from the strengths of both models. Data is held once but can be accessed, written, and modified with either approach. Developers benefit from ACID-compliant transactions and concurrency controls, which means they no longer have to make trade-offs between complex object-relational mappings or data inconsistency issues.

JSON Schema

Oracle Database supports JSON to store and process schema-flexible data. With Oracle Database 23c, Oracle Database now supports JSON Schema to validate structure and values of JSON data. The SQL operator IS JSON was enhanced to accept a JSON Schema, and various PL/SQL functions were added to validate JSON and to describe database objects such as tables, views, and types as JSON Schema documents.

By default, JSON data is schemaless, providing flexibility. However, you may want to ensure that JSON data has a particular structure and typing, which can be done via industry-standard JSON Schema validation.

Contribute to JSON Schema
Oracle actively contributes to JSON Schema, an open source effort to standardize a JSON-based declarative language that allows you to annotate and validate JSON documents. It is currently in Request for Comments (RFC).

PL/SQL JSON constructor support for aggregate types

The PL/SQL JSON constructor is enhanced to accept an instance of a corresponding PL/SQL aggregate type, returning a JSON object or array type populated with the aggregate type data.

The PL/SQL JSON_VALUE operator is enhanced so its returning clause can accept a type name that defines the type of the instance that the operator is to return. JSON constructor support for aggregate data types streamlines data interchange between PL/SQL applications and languages that support JSON.

MongoDB-compatible API

With the Oracle Database API for MongoDB, developers can continue to use MongoDB's tools and drivers connected to an Oracle Database while gaining access to Oracle's multimodel capabilities and self-driving database. Customers can run MongoDB workloads on Oracle Cloud Infrastructure (OCI). Often, little or no changes are required to existing MongoDB applications—you simply need to change the connection string.

The Oracle Database API for MongoDB is part of standard Oracle REST Data Services. It is preconfigured and fully managed as part of the Oracle Autonomous Database.

  • As we wrap up 2023, here's a recap of the new features in Oracle Database 23c that we highlighted throughout the year...

  • Oracle Database 23c introduces an online migration tool that simplifies migration from Oracle Advanced Queuing (AQ) to Transactional Event Queues (TxEventQ) with orchestration automation, source, and target compatibility diagnostics and remediation and a unified user experience...

  • Oracle Database 23c provides even more refined compatibility for Apache Kafka applications with Oracle Database...

  • Lock-Free Reservations enable concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-Free Reservations are held on the rows instead of locking them...

  • Oracle continues to expand its cloud native and Kubernetes support with our new Observability Exporter for Oracle Database...

  • The Saga framework introduced in Oracle Database 23c provides a unified framework for building async Saga applications in the database. ..

AQ to TxEventQ Online Migration Tool

Oracle Database 23c introduces an online migration tool that simplifies migration from Oracle Advanced Queuing (AQ) to Transactional Event Queues (TxEventQ) with orchestration automation, source, and target compatibility diagnostics and remediation and a unified user experience. Migration scenarios can be short- or long-lived and be performed with or without AQ downtime, eliminating operational disruption.

Existing AQ customers interested in higher throughput queues and with Kafka compatibility using a Kafka Java Client and Confluent-like REST APIs, can easily migrate from AQ to TxEventQ. TxEventQ offers scalability, performance, key-based partitioning, and native JSON payload support, which makes event-driven microservices/application writing easier in multiple languages, including Java, JavaScript, PL/SQL, Python, and more.

Kafka APIs

Oracle Database 23c provides even more refined compatibility for Apache Kafka applications with Oracle Database. This new feature provides easy migration for Kafka Java applications to Transactional Event Queues (TxEventQ). Kafka Java APIs can now connect to Oracle Database server and use TxEventQ as a messaging platform.

Developers can easily migrate an existing Java application that uses Kafka to Oracle Database using the JDBC thin driver. And with the Oracle Database 23c client-side library feature, Kafka applications can now connect to Oracle Database instead of a Kafka cluster and use TxEventQ's messaging platform transparently.

Lock-free column value reservations

Lock-Free Reservations enable concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-Free Reservations are held on the rows instead of locking them. It verifies if the updates can succeed and defers the updates until the transaction commit time. Lock-Free Reservations improves the user experience and concurrency in transactions.

Grafana observability

Oracle continues to expand its cloud native and Kubernetes support with our new Observability Exporter for Oracle Database, which allows customers to easily export database and application metrics in industry-standard Prometheus format, and to easily create Grafana dashboards to monitor the performance of their Oracle Databases and applications.

Saga APIs in Oracle Database 23c

The Saga framework introduced in Oracle Database 23c provides a unified framework for building async Saga applications in the database. Saga makes modern, high performance microservices application development easier and more reliable.

A Saga is a business transaction spanning multiple databases, implemented as a series of independent local transactions. Sagas avoid the global transaction duration locking found with synchronous distributed transactions and simplify consistency requirements for maintaining a global application state. The Saga framework integrates with Lock-Free reservable columns in Oracle Database 23c to provide automatic Saga compensation, simplifying application development.

The Saga framework emulates the MicroProfile LRA specification.