Database 23ai: Feature Highlights

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.

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.

DBMS_SEARCH implements Oracle Text ubiquitous search. DBMS_SEARCH makes it very easy to create a single index over multiple tables and views. Just create a DBMS_SEARCH index and add tables and views. All searchable values, including VARCHAR, CLOB, JSON, and numeric columns will be included in the index, which is automatically maintained as the table or view contents change.

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.

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.

Real-Time SQL Plan Management (SPM) quickly detects and repairs SQL performance problems caused by execution plan changes.

If a SQL statement has been running well, but a plan change causes it to perform poorly, Real-Time SPM immediately detects it. If it establishes that a previous plan will perform better, Real-Time SPM will reinstate it using a SQL plan baseline.

This automates what some DBAs do already: They create SQL plan baselines to target individual SQL statements with intermittent performance issues and enforce a plan that is known to be good.

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.

True Cache (TC) is an in-memory, consistent, and automatically managed cache for Oracle Database. It operates similarly to an Oracle Active Data Guard reader farm, except that True Cache instances are mostly diskless and designed for performance and scalability as opposed to disaster recovery. An application can connect to True Cache instances directly for read-only workloads. A general read/write Java application can also simply mark some sections of code as read-only, and the Oracle Database 23ai True Cache JDBC driver can automatically send read-only workloads to configured True Cache instances.

Today, many Oracle users place a cache in front of Oracle Database to speed up query response time and improve overall scalability. True Cache is a new way to have a cache in front of the Oracle Database. True Cache has many advantages, including ease of use, consistent data, more recent data, and an automatically managed cache.

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.

Oracle Database 23ai introduces a new unified audit capability with selectivity at the column level that lets you create more narrowly targeted audit policies that reduce "noise" from unnecessary audit records.

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.

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.

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. The Boolean data type standardizes the storage of Yes and No values and makes it easier to migrate to Oracle Database.

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.

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.

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.

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.

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.

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

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.

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.

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

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

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.

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.

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.

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.

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

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

• Review Oracle's contributions to JSON Schema and comment
• Or you can contribute via GitHub

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.

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.

Oracle AI Vector Search is a new converged database capability introduced in Oracle Database 23ai. It uses vectors to enable fast and simple similarity search queries on both structured and unstructured data. AI Vector Search also enables prompts to large language models (LLMs) to be augmented with private business data or domain knowledge.

AI Vector Search stores vectors as a native data type and uses vector indexes and SQL functions to run a similarity search on the vectors. With this capability, customers can quickly identify similar information from documents, images, and other unstructured data.

AI Vector Search makes it simple to quickly search both structured and unstructured data and combine these results with results from traditional database queries. Source data of virtually any type and the vectors representing it can be stored together in the same database, reducing IT complexity and helping maintain data consistency. AI Vector Search, combined with retrieval-augmented generation (RAG), helps provide LLM users with more accurate responses and helps reduce hallucinations.

AI Vector Search makes it easy for developers to add and maintain similarity search capabilities to existing applications and databases or create new ones. Two use cases apply to nearly all industries. Because all customers seek simplicity, Oracle offers AI/ML without the need for a PhD in data science.

1. Similarity searches—searches for similar content in unstructured data (text, audio, images, video, and so forth). The unstructured data could be files outside the database or BLOBs/CLOBs inside the database. Oracle excels at this and Exadata makes this extremely fast and scalable.
2. RAG—this makes the best use of LLMs and helps minimize their problems. LLMs can lie (hallucinations), have out-of-date data (i.e., the training cutoff) and only know generic information from training on the internet. Oracle uses pretrained LLMs and pretrained vector embedding models to do the difficult work. Oracle AI Vector Search uses the vector generated by the vector embedding models and interacts with the LLMs.

Oracle Database offers native support for property graph data structures and graph queries. If you're looking for flexibility to build graphs in conjunction with transactional data, JSON, Spatial, and other data types, we got you covered. Developers can now easily build graph applications with SQL using existing SQL development tools and frameworks.

We believe that AppDev shouldn’t be complex and time-consuming. The latest generative AI technologies give us a great opportunity to push state-of-the-art low-code AppDev even further. Oracle APEX 24.1 delivers on three primary pillars of innovation that enable you to build compelling enterprise-grade apps with ease: AI-assisted app development, harnessing the power of Oracle’s next-gen data platform, and powerful enterprise-grade components for building sophisticated cloud and mobile apps.

Oracle APEX is a fully supported, no-cost feature of Oracle Database and all Oracle Database services, including Oracle Autonomous Database that developers can try for free here.

As we wrap up 2023, here's a recap of the new features in Oracle Database 23c that we highlighted throughout the year. If you haven't had a chance to try out our latest Oracle Database release yet—especially if you’re a developer—check out the different options here or at oracle.com/database/free.

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.

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

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.

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.