Oracle AI Database 26ai : 50 Key Features Every DBA Should Know

 

Oracle AI Database 26ai represents an important evolution in Oracle’s database technology. It introduces significant enhancements across artificial intelligence, SQL and PL/SQL, performance optimization, high availability, security, database administration, and distributed database architectures.

This guide covers 50 important features and enhancements, focusing on their technical capabilities, practical use cases, and benefits for database administrators.

The examples are intended to illustrate usage patterns. Exact syntax and availability should always be verified against the documentation for the installed Oracle Database 26ai Release Update.

SECTION 1 – SQL and PL/SQL Enhancements

1. SQL BOOLEAN Data Type

Oracle extends BOOLEAN support to SQL, allowing Boolean values to be stored and queried directly in database tables.

Example:

CREATE TABLE employee_status (
    employee_id NUMBER,
    employee_name VARCHAR2(100),
    is_active BOOLEAN
);

INSERT INTO employee_status
VALUES (1, 'JOHN', TRUE);

SELECT *
FROM employee_status
WHERE is_active = TRUE;

Benefits for DBAs:

• Better SQL and PL/SQL type compatibility.
• Simplified application development.
• Native support for Boolean values in SQL expressions.

2. GROUP BY ALL

GROUP BY ALL simplifies SQL statements by automatically grouping selected non-aggregate expressions.

SELECT department_id,
       job_id,
       COUNT(*) employee_count
FROM employees
GROUP BY ALL;

This is particularly useful in dynamically generated SQL statements.

3. QUALIFY

QUALIFY allows filtering based on analytic function results without requiring an additional subquery.

SELECT employee_id,
       department_id,
       salary,
       ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) rn
FROM employees
QUALIFY rn <= 3;

This example returns the three highest-paid employees in each department.

4. DATEDIFF

DATEDIFF calculates the difference between two dates or timestamps using a specified time unit.

SELECT DATEDIFF(
    DAY,
    DATE '2026-01-01',
    DATE '2026-10-31'
) AS day_difference
FROM dual;

It simplifies date and time calculations.

5. SELECT Without FROM

Oracle supports SELECT statements without an explicit FROM clause.

SELECT 100 + 200 AS total,
       CURRENT_DATE AS today;

This is useful for simple calculations and application-generated SQL.

6. GROUP BY Column Alias

Column aliases can be used in GROUP BY expressions.

SELECT department_id AS dept,
       SUM(salary) AS total_salary
FROM employees
GROUP BY dept;

This makes SQL statements easier to read and maintain.

7. Direct Joins for UPDATE and DELETE

Enhanced DML capabilities simplify updates and deletions involving related tables.

A conceptual example:

UPDATE employees e
SET salary = salary * 1.10
FROM departments d
WHERE e.department_id = d.department_id
  AND d.department_name = 'IT';

Always verify the exact supported syntax in the 26ai SQL Language Reference.

8. SQL Assertions

SQL Assertions allow business rules and data integrity conditions to be defined across multiple database tables.

Examples include:

• Preventing invalid business transactions.
• Enforcing cross-table consistency.
• Centralizing business rules at the database level.

This reduces dependence on application-level validation.

9. Data Use Case Domains

Data Use Case Domains provide reusable definitions for data types and business rules.

They help standardize data definitions across database applications and improve consistency.

10. FUZZY_MATCH and Text Matching

Fuzzy matching capabilities help identify similar text values.

Typical use cases include:

• Detecting duplicate customer records.
• Identifying spelling variations.
• Data cleansing and standardization.
• Matching names with minor differences.

For example, identifying similar values such as “Oracle Database” and “Oracle Databse”.

SECTION 2 – PL/SQL and Application Development

11. RESETTABLE Clause

The RESETTABLE clause allows package developers to indicate that a package can safely lose its current state and be re-instantiated.

Example:

CREATE OR REPLACE PACKAGE pkg_test
RESETTABLE
AS
    PROCEDURE process_data;
END pkg_test;
/

This is relevant to package invalidation and re-instantiation scenarios, including certain cases associated with ORA-04068.

It should only be used when losing the package state is safe for the application.

12. JavaScript Stored Procedures

Oracle supports JavaScript execution inside the database through its Multilingual Engine (MLE).

Example JavaScript logic:

function calculateTax(amount, rate) {
    return amount * rate / 100;
}

This code can be deployed as a JavaScript module and exposed to SQL or PL/SQL through the supported MLE interfaces.

13. JSON Relational Duality

JSON Relational Duality allows the same underlying data to be accessed through both relational tables and JSON documents.

Example:

CREATE JSON RELATIONAL DUALITY VIEW employee_dv AS
SELECT JSON {
    '_id'       : employee_id,
    'name'      : first_name,
    'salary'    : salary
}
FROM employees;

Key benefits:

• Simplified JSON application development.
• Reduced ORM complexity.
• Relational data integrity.
• Multiple application access models over the same data.

14. GraphQL Support

GraphQL integration capabilities make it easier to expose database data to modern applications.

This is particularly useful for microservices and applications that prefer GraphQL APIs over traditional REST interfaces.

15. Operational Property Graph

Oracle provides graph capabilities over relational data.

Typical use cases:

• Customer relationship analysis.
• Fraud detection.
• Network analysis.
• Enterprise data relationship discovery.

16. Kafka API for TxEventQ

Enhancements to Transactional Event Queues improve integration with Kafka-based applications.

DBAs can manage messaging workloads within Oracle Database while supporting modern event-driven application architectures.

17. Lock-Free Reservations

Lock-Free Reservations provide mechanisms for reserving portions of numeric values without relying on traditional row-level locking behavior in supported scenarios.

Potential use cases:

• Bank account balance reservations.
• Inventory allocation.
• Capacity management.
• High-concurrency OLTP workloads.

18. Priority Transactions

Priority Transactions allow transaction priorities to be considered when managing transaction contention.

Example configuration:

ALTER SYSTEM SET
    PRIORITY_TXNS_HIGH_WAIT_TARGET = 10
    SCOPE=BOTH;

This capability is particularly relevant to critical OLTP environments.

The exact parameter settings and supported behavior must be verified for the installed release.

19. DB_DEVELOPER_ROLE

DB_DEVELOPER_ROLE provides a predefined privilege model for database developers.

GRANT DB_DEVELOPER_ROLE TO APP_DEV;

It helps reduce the need to grant broad administrative privileges to developers.

20. Schema-Level Privileges

Schema-level privilege enhancements simplify access management across database schemas.

They are particularly useful in large enterprise environments where managing individual object privileges becomes complex.

SECTION 3 – Performance Tuning and Database Administration

21. Automatic Data Clustering

Automatic Data Clustering improves physical data organization based on access patterns.

Benefits include:

• Reduced I/O operations.
• Improved query performance.
• Better data locality.
• More efficient analytical workloads.

22. Enhanced Automatic Indexing

Automatic Indexing capabilities help identify and manage indexes based on workload analysis.

Example monitoring query:

SELECT *
FROM DBA_AUTO_INDEX_EXECUTIONS
ORDER BY EXECUTION_START DESC;

DBAs should monitor index creation, validation, and execution plan changes.

23. Enhanced Automatic SQL Plan Management

SQL Plan Management enhancements help preserve execution plan stability.

Example:

SELECT SQL_HANDLE,
       PLAN_NAME,
       ENABLED,
       ACCEPTED,
       FIXED
FROM DBA_SQL_PLAN_BASELINES;

This is especially important after database upgrades and optimizer changes.

24. Enhanced Automatic Materialized Views

Materialized View management and automatic optimization capabilities have been enhanced.

Example:

SELECT MVIEW_NAME,
       REFRESH_MODE,
       REFRESH_METHOD,
       STALENESS
FROM DBA_MVIEWS;

These enhancements help reduce repetitive calculations in reporting and analytical systems.

25. Real-Time SQL Monitoring Enhancements

SQL Monitoring provides enhanced visibility into SQL execution activity across database environments.

Example:

SELECT SQL_ID,
       STATUS,
       SQL_EXEC_START,
       ELAPSED_TIME,
       CPU_TIME
FROM V$SQL_MONITOR
ORDER BY SQL_EXEC_START DESC;

This is useful for identifying expensive SQL statements and analyzing execution behavior.

26. Automatic In-Memory Sizing

Automatic In-Memory management improves the allocation and utilization of the In-Memory Column Store.

Monitoring:

SHOW PARAMETER inmemory;

SELECT POOL,
       ALLOC_BYTES,
       USED_BYTES,
       POPULATE_STATUS
FROM V$INMEMORY_AREA;

27. Memory Speed Columnar

Memory-oriented columnar processing enhancements improve analytical data access.

Typical workloads include:

• Large analytical queries.
• Data warehouse operations.
• Column-oriented processing.
• In-memory analytics.

28. Automatic SecureFiles Shrink

SecureFiles LOB management enhancements improve storage utilization and reclamation.

Example monitoring query:

SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       SEGMENT_NAME,
       SECUREFILE
FROM DBA_LOBS;

This is particularly useful for applications with frequent LOB updates and deletions.

29. Smallfile Tablespace Shrink

Tablespace management enhancements improve the ability to reclaim unused space in supported configurations.

Example:

SELECT TABLESPACE_NAME,
       FILE_ID,
       FILE_NAME,
       BYTES / 1024 / 1024 AS SIZE_MB
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME, FILE_ID;

Before shrinking datafiles, DBAs should analyze the high-water mark and allocated blocks.

30. WAIT and NOWAIT Support

Enhanced WAIT and NOWAIT capabilities provide more control over lock waiting behavior in supported DML operations.

The objective is to allow applications to control how long they wait for database resources rather than relying entirely on default locking behavior.

SECTION 4 – RAC, ASM and High Availability

31. Smooth Reconfiguration of Oracle RAC Instances

Oracle RAC configuration management improvements aim to reduce the impact of instance reconfiguration on application availability.

Important areas include:

• Instance maintenance.
• Service relocation.
• Node management.
• Connection continuity.

32. Oracle RAC Two-Stage Rolling Updates

Rolling maintenance capabilities have been enhanced to support controlled updates across RAC nodes.

Example RAC checks:

srvctl config database -db PROD
srvctl status database -db PROD

Oracle Fleet Patching and Provisioning (FPP) should also be considered for enterprise patch automation.

33. Local Rolling Database Maintenance

Local Rolling Database Maintenance provides additional maintenance flexibility by allowing supported maintenance operations within a node using a new Oracle Home.

Potential benefits:

• Reduced application downtime.
• Out-of-place maintenance.
• Improved maintenance flexibility.
• Better control over planned maintenance windows.

34. Ordered Sequence Optimizations in Oracle RAC

Sequence-related optimizations improve performance in RAC environments.

Example:

SELECT SEQUENCE_OWNER,
       SEQUENCE_NAME,
       CACHE_SIZE,
       ORDER_FLAG
FROM DBA_SEQUENCES;

DBAs should evaluate sequence configuration in high-transaction environments.

35. Smart Connection Rebalance

Smart Connection Rebalance improves connection distribution recommendations across database instances and services.

Example:

SELECT INST_ID,
       COUNT(*) AS SESSION_COUNT
FROM GV$SESSION
WHERE TYPE = 'USER'
GROUP BY INST_ID
ORDER BY INST_ID;

This helps identify connection imbalances in RAC environments.

36. Cluster Health Monitor Improvements

Cluster Health Monitor enhancements improve infrastructure diagnostics.

Important monitoring areas:

• CPU utilization.
• Memory pressure.
• Network issues.
• Cluster resource behavior.
• Node health.

37. ACFS Enhancements

Oracle ACFS introduces improvements in storage and replication management.

Important areas include:

• Automatic sizing thresholds.
• Cross-version replication.
• Passwordless SSH configuration.
• Replication switchover.
• Additional replication management options.

Example:

acfsutil info fs
acfsutil info storage

38. Bigfile Tablespace Defaults

Oracle 26ai introduces Bigfile tablespace defaults in DBCA templates, including SYSTEM, SYSAUX, and USER tablespaces.

Example:

CREATE BIGFILE TABLESPACE APP_DATA
DATAFILE '+DATA'
SIZE 20G
AUTOEXTEND ON NEXT 1G
MAXSIZE 500G;

This simplifies datafile management, although storage architecture and file-size limitations must be considered.

39. Consolidated Service Backgrounds

Oracle introduces architectural improvements involving the consolidation of certain service-related background processes.

The objective is to optimize process management and resource utilization.

This is an internal database architecture enhancement rather than a feature configured through a direct SQL statement.

40. Automatic Transaction Quarantine

Automatic Transaction Quarantine provides mechanisms for handling problematic transactions that repeatedly cause performance or resource issues.

It aims to reduce operational intervention in supported scenarios.

SECTION 5 – Security and Database Protection

41. Oracle SQL Firewall

Oracle SQL Firewall is one of the most important security capabilities in Oracle AI Database 26ai.

It learns normal SQL activity and creates an Allow List based on observed application behavior.

Step 1 – Enable SQL Firewall

EXEC DBMS_SQL_FIREWALL.ENABLE;

Step 2 – Start SQL Capture

BEGIN
    DBMS_SQL_FIREWALL.CREATE_CAPTURE(
        username       => 'APP',
        top_level_only => TRUE,
        start_capture  => TRUE
    );
END;
/

Step 3 – Stop Capture

EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('APP');

Step 4 – Generate Allow List

EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('APP');

Step 5 – Enable Enforcement

BEGIN
    DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
        username => 'APP',
        enforce  => DBMS_SQL_FIREWALL.ENFORCE_ALL,
        block    => TRUE
    );
END;
/

Step 6 – Review Violations

SELECT *
FROM DBA_SQL_FIREWALL_VIOLATIONS;

Important benefits:

• Detects unauthorized SQL statements.
• Blocks SQL outside the approved Allow List.
• Helps protect against SQL injection attacks.
• Provides additional control over database connections.

Production deployment requires careful workload capture and testing.

42. TLS 1.3 Support

Oracle Net supports TLS 1.3 for secure database communications.

Example connectivity test:

openssl s_client \
-connect dbserver:2484 \
-tls1_3

Benefits include stronger encryption and improved connection security.

43. Quantum-Resistant Cryptography

Oracle introduces support for quantum-resistant cryptographic capabilities.

These technologies are designed to protect sensitive information against future threats posed by quantum computing.

DBAs should evaluate their compatibility with TDE, Oracle Net, Wallet, and certificate management.

44. Password Length Enhancement

Oracle 26ai increases the maximum supported password length to 1024 bytes.

Example:

ALTER USER APP
IDENTIFIED BY "Very_Long_Strong_Password";

This supports stronger enterprise password policies.

45. DB_DEVELOPER_ROLE and Least Privilege

Predefined developer privileges help organizations implement a more controlled access model.

GRANT DB_DEVELOPER_ROLE TO APP_DEV;

The objective is to avoid granting unnecessary administrative privileges to application developers.

SECTION 6 – Data Guard, RMAN and Disaster Recovery

46. Data Guard per-PDB

Oracle 26ai introduces additional flexibility for Data Guard management at the PDB level.

Important capabilities include:

• PDB-level switchover.
• PDB-level failover.
• Automated standby PDB provisioning enhancements.
• Standby Redo Log management improvements.
• PDB-level Real-Time Query capabilities.

These enhancements are particularly valuable in large multitenant environments.

47. Automatic Block Repair for PDB

Automatic Block Repair capabilities are enhanced for PDB environments.

In supported Active Data Guard configurations, a corrupted block on the primary database can be repaired using a healthy copy from the standby database.

Benefits:

• Reduced corruption-related downtime.
• Improved data availability.
• Less manual recovery intervention.

48. Restrict Switchover and Failover

Data Guard Broker provides controls for restricting which databases can become the primary database.

Example:

DGMGRL> EDIT CONFIGURATION
  SET PROPERTY 'PrimaryDatabaseCandidates'
  = 'PROD, PROD_DR';

This allows administrators to define eligible primary database candidates.

49. RMAN Immutable Backups

Immutable backups provide an additional layer of protection against ransomware and unauthorized backup deletion.

Important capabilities:

• Protection against backup modification.
• Retention-based deletion protection.
• Integration with locked Object Storage retention policies.
• Improved recovery protection.

A simplified architecture:

Oracle Database
       |
      RMAN
       |
 OCI Object Storage
       |
 Immutable Retention Lock

This is especially important for enterprise backup and disaster recovery strategies.

50. Oracle Globally Distributed Database – RAFT Replication

Oracle 26ai introduces RAFT replication capabilities for Globally Distributed Database architectures.

RAFT provides:

• Leader election.
• Replica coordination.
• Majority-based consensus.
• Synchronous replication.
• Strong consistency.

Conceptual architecture:

             Application
                  |
             Shard Catalog
                  |
       -----------------------
       |          |          |
     Shard 1    Shard 2    Shard 3
     Leader     Replica    Replica
       |          |          |
       -------- RAFT --------

RAFT is particularly important for distributed database environments requiring coordinated replication and automatic leader selection.

It is different from the traditional Data Guard primary/standby architecture and should be evaluated as part of a distributed database design.

Conclusion

Oracle AI Database 26ai is more than a database release introducing new SQL functions. It represents a broader evolution in database architecture, automation, security, high availability, and AI integration.

For experienced Oracle DBAs, the most important areas to explore are:

• SQL Firewall and database security.
• True Cache and performance optimization.
• Data Guard per-PDB capabilities.
• RAC rolling maintenance.
• RMAN immutable backups.
• AI Vector Search and distributed database architecture.

Understanding these capabilities will help database administrators design more secure, scalable, highly available, and intelligent database infrastructures.