1 Oracle Database 12.2 New Features

This chapter contains overview descriptions of all of the features that are new to Oracle Database 12.2.

Application Development

Database Development Productivity Tools Enhancements

Application Express 5.0: All New Calendar

The new calendar component includes built-in support for Month, Week, Day, and Agenda views, and is much easier to customize. The calendar is based on the popular FullCalendar library and supports drag and drop, time-based events, and is even responsive. The ability to easily define duration-based events and restyle the calendars, makes the new calendar very popular with both developers and end-users.

Oracle Application Express release 5.0 calendars now support duration-based events and are significantly easier to style.

Application Express 5.0: Improved Application Builder Design

Oracle Application Express release 5.0 introduces a new user interface that focuses on improving the user experience through simplicity and the removal of clutter. The new design uses a beautiful new color palette, carefully crafted icons, and vastly improved menus and navigation. The new design also provides improved accessibility and keyboard support, more intuitive page layouts, and many other enhancements.

Oracle Application Express release 5.0 Application Builder is more intuitive and productive for developers.

Application Express 5.0: Interactive Reporting

Interactive reports have been completely rebuilt in Oracle Application Express release 5.0 to enhance both developer and end-user capabilities. New capabilities include the ability to define multiple reports on a single page, column pivot, fixed headers, and modernized actions. You can also restyle interactive report regions using Cascading Style Sheets (CSS) in a similar manner to other regions within Oracle Application Express.

Oracle Application Express release 5.0 interactive reports enhances both developer and end-user capabilities.

Application Express 5.0: Mobile Enhancements

You can now build reports that display all of your data on any mobile device by using reflow table or column toggle. Reflow table wraps each column or changes the display to allow multiple lines on very small screens. Column toggle enables you to specify the most important columns to view and those columns that should be hidden, as necessary, on smaller screens. Panels are now incorporated into mobile applications and are used to display navigation menus.

Oracle Application Express release 5.0 mobile improvements enable the development of improved mobile-first applications.

Application Express 5.0: Modal Dialog Pages

Now you can easily define modal and non-modal pages, complete with the ability to use standard page processes. You no longer need to manually edit a page using JavaScript. Instead, set the display type and the appropriate template and let Oracle Application Express take care of the rest.

Oracle Application Express release 5.0 modal dialog pages make it easy for developers to display pages modally, as opposed to writing significant amounts of custom code.

Application Express 5.0: Packaged Applications

Oracle Application Express release 5.0 includes a broad collection of point solutions called packaged applications. These are Application Express applications that you can use out-of-the-box and that are supported by Oracle. Examples include Project Tracking, Survey Builder, Meeting Minutes, and Group Calendar. There are 19 productivity applications in all. Additionally, there are 16 sample applications that are used to showcase the features of Oracle Application Express, from Sample Charts to Sample Data Loading. Release 5.0 even includes a sample application that demonstrates the powerful spatial capabilities that are present in every Oracle Database.

Oracle Application Express release 5.0 packaged applications are very popular as point solutions and learning examples.

Application Express 5.0: Page Designer

Page Designer is a modern, intuitive, and exceedingly powerful browser-based Integrated Development Environment (IDE). As a completely new IDE that is designed to greatly improve developer productivity, Page Designer enables you to very quickly develop and maintain your Application Express applications. Page Designer features a better visual representation of your application pages and provides an entirely new way of quickly developing pages by using intuitive drag and drop. The enhanced code editor provides SQL and PL/SQL validation with inline errors, auto completion, syntax highlighting, search and replace with regex support, complete with undo and redo support.

Oracle Application Express Page Designer greatly improves developer productivity, provides a cohesive user experience, better visual representation, intuitive drag and drop, and an enhanced code editor.

Application Express 5.0: Universal Theme

Universal Theme is a new user interface for your applications that have been built from the ground up for Oracle Application Express release 5.0. It is a simpler, yet more capable theme that eliminates excessive templates and enables customization using the built-in Theme Roller and Template Options. The Universal Theme enables developers to build modern, responsive, and sophisticated applications without requiring expert knowledge of HTML, Cascading Style Sheet (CSS), or JavaScript.

Oracle Application Express Universal Theme provides a number of new capabilities including Theme Roller, Template Options, responsive design, and accessibility.

Application Express 5.0: User Interface Enhancements

With the development of Universal Theme, there are also several enhancements to the handling of themes and templates. Oracle Application Express release 5.0 includes features such as Theme Subscriptions, Template Options, and Theme Styles. These features give you more granular control over your templates and the HTML that the Application Express engine produces. It is easier to fully control your application user interface.

Oracle Application Express release 5.0 enables developers to easily build modern, responsive, and beautiful applications out-of-the-box.

Oracle SQL and PL/SQL Improvements

Enhanced CAST Function With Error Handling

The existing CAST function is enhanced to return a user-specified value in the case of a conversion error instead of raising an error.

This new functionality provides more robust and simplified code development.


The new SQL function, VALIDATE_CONVERSION, determines whether a given input value can be converted to the requested data type.

The VALIDATE_CONVERSION function provides more robust and simplified code development.

Enhanced LISTAGG Functionality

LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality has been added for managing situations where the length of the concatenated string is too long.

Developers can now control the process for managing overflowing LISTAGG aggregates. This increases the productivity and flexibility of this aggregation function.

Approximate Query Processing

This release extends the area of approximate query processing by adding approximate percentile aggregation. With this feature, the processing of large volumes of data is significantly faster than the exact aggregation. This is especially true for data sets that have a large number of distinct values with a negligible deviation from the exact result.

Approximate query aggregation is a common requirement in today's data analysis. It optimizes the processing time and resource consumption by orders of magnitude while providing almost exact results. Approximate query aggregation can be used to speed up existing processing.

Materialized Views: Real-Time Materialized Views

Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables and are considered stale. Using materialized view logs for delta computation together with the stale materialized view, the database can compute the query and return correct results in real time.

For materialized views that can be used for query rewrite all of the time, with the accurate result being computed in real time, the result is optimized and fast query processing for best performance. This alleviates the stringent requirement of always having to have fresh materialized views for the best performance.

Materialized Views: Statement-Level Refresh

In addition to ON COMMIT and  ON DEMAND refresh, materialized join views can be refreshed when a DML operation takes place, without the need to commit such a transaction. This is predominantly relevant for star schema deployments.

The new ON STATEMENT refresh capability provides more flexibility for application developers to take advantage of materialized view rewrite, especially for complex transactions involving multiple DML statements. It offers built-in refresh capabilities that can replace customer-written trigger-based solutions, simplifying an application while offering higher performance.

JSON Support

JSON Improvements

This release incorporates significant support enhancements for storing and querying JavaScript Object Notation (JSON) documents for Oracle Database. Of particular interest are:

  • Improvements to JSON searching:
    • The JSON path expressions used with simplified syntax for querying JSON now support navigating to specific members of an array.
    • JSON path expressions used with JSON_EXISTS condition now support predicates.
  • Improvements to JSON search index:
    • A new, simplified syntax makes it easier to create a JSON search index.
    • The JSON search index supports RANGE and LIST partitioned tables.
    • The JSON search index support range-based searching on numeric values.
    • The JSON search index can now deal with large keys
  • New capabilities for generating JSON documents directly from SQL queries.
  • Support for manipulating JSON documents using PL/SQL. This includes the ability to make incremental modifications to JSON documents.
  • Support for optimizing the performance for JSON query operations using Oracle Database In-Memory.
  • Support for performing spatial-based queries on JSON documents containing GeoJSON.
  • A new Oracle data guide feature that facilitates understanding the structure and content of your JSON documents.

This feature makes it easier to work with JSON documents stored in an Oracle database and to generate JSON documents from relational data.


Online Operations

Online Conversion of a Nonpartitioned Table to a Partitioned Table

Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.

The online conversion of a nonpartitioned table to a partitioned table enables any application to adopt partitioning without application downtime. Customers can adopt partitioning for any system and evolve tables as needed to benefit from the partitioning for large tables.

Online SPLIT Partition and Subpartition

The partition maintenance operations SPLIT PARTITION and SPLIT SUBPARTITION can now be executed as online operations for heap organized tables, allowing the concurrent DML operations with the ongoing partition maintenance operation.

Allowing any partition maintenance operation to become truly online enables the customers to schedule and execute all of these operations as needed, without having to plan around periods of query-only windows. This functionality both increases application availability and simplifies application development.

Online Table Move

Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.

Data maintenance on nonpartitioned tables does not require any maintenance window since it does not impact any DML or query operation.

Big Data and Data Warehousing

Dimensional In-Database Analysis

Analytic views provide application developers with the ability to define a dimensional query and calculation layer over tables and other objects in the database. Analytic views simplify business intelligence application development, enhance data sets with dimensional, hierarchical and time series calculations, and allow metadata and calculation definitions to be centrally defined and maintained in Oracle Database.

Analytic Views

Analytic views provide a business intelligence layer over a star schema, making it easy to extend the data set with hierarchies, levels, aggregate data, and calculated measures. The analytic view feature includes the new DDL statements CREATE ATTRIBUTE DIMENSION, CREATE HIERARCHY and CREATE ANALYTIC VIEW and their related ALTER and DROP statements, new calculated measure expression syntax, and new data dictionary views.

Analytic views allow data warehouse and business intelligence application developers to extend the star schema with time series and other calculations, making data more valuable to business users and eliminating the need to define calculations within the application.

Analytic views can be queried with simple SQL queries, simplifying application development by eliminating the need for complex SQL generators. Calculations can be defined in the analytic view and can be selected by including the measure name in the SQL select list.

Analytic views promote consistency across applications. By defining aggregation and calculation rules centrally in the database, the risk of inconsistent results in different reporting tools is reduced or eliminated.

Compression and Archiving

Index Compression Enhancements

Advanced Index Compression

Prior to this release, the only form of advanced index compression was low compression. Now you can also specify high compression. High compression provides even more space savings than low compression.

Indexes consume a large amount of storage within many databases. The high level of advanced index compression provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the following advantages over low compression:

  • Gives higher compression ratios in most cases.
  • Employs more complex compression algorithms than advanced low compression.
  • Stores data in a compression unit, which is a special on-disk format.

Database Overall

Core Database Improvements

Core database improvements.

Long Identifiers

The maximum length of identifiers is increased to 128 bytes for most identifiers, up from 30 bytes in previous releases.

Providing longer identifiers gives customers greater flexibility in defining their naming schemes, such as longer and more expressive table names. Having longer identifiers also enables object name migration between databases with different character sets, such as Thai to Unicode.

Text: Read-Only MDATA Sections

Normal MDATA sections can be updated without reindexing the entire document, but there is a performance cost for doing so. Now you have the option of specifying MDATA sections as read-only, which means they can only be changed when the document is updated and the index is synchronized.

This feature provides better performance for queries because there is no extra cursor required to handle read-only MDATA sections. Reducing the number of cursors required may also prevent exceeding the limit of the OPEN_CURSORS system parameter.

Text: Sentiment Analysis and Collocates

Oracle Text supports sentiment analysis and collocates. Sentiment analysis provides identification of positive and negative trends associated with search terms.

The identification of positive or negative trends associated with search terms allows the building of richer search applications.

Partitioning: Auto-List Partitioning

The database automatically creates a separate (new) partition for every distinct partition key value of the table.

Auto-list partitioning removes the management burden from the DBAs to manually maintain a list of partitioned tables for a large number of distinct key values that require individual partitions. It also automatically copes with the unplanned partition key values without the need of a DEFAULT partition.

Partitioning: Multi-Column List Partitioning

List partitioning functionality is expanded to allow multiple partition key columns.

Use multiple columns to define the partitioning criteria for list partitioned tables enables new classes of applications to benefit from partitioning.

Partitioning: Read-Only Partitions

Partitions and sub-partitions can be individually set to a read-only state.  This then disallows DML operations on these read-only partitions and sub-partitions. This is an extension to the existing read-only table functionality.

Read-only partitions and subpartitions enables fine-grained control over DML activity. This enhances the data management capabilities of partitioned tables.

Partitioning: Table Creation for Partition Exchange

A new DDL command allows the creation of a table that exactly matches the shape of a partitioned table and is therefore eligible for a partition or subpartition exchange of a partitioned table. Note that indexes are not created as part of this command.

Creating a table that qualifies for a partition or subpartition exchange can be a tedious task for older tables that have had various structural changes and reorganizations. With this new DDL, the task becomes very simple and straightforward to implement. It also adds some implicit business context to such an operation, as compared to a CREATE TABLE AS SELECT command.

Partitioning: Filtered Partition Maintenance Operations

Partition maintenance operations can now be combined with data filtering. For example, a partition can be compressed and moved to a different tablespace, but only the data satisfying the specific filter criteria is actually moved.

Partition maintenance operations with data filtering combine two of the most common data maintenance operations. This combination not only makes the partition maintenance operation more flexible and powerful, but also makes it more performant and less resource intensive compared to the two separate data management operations.

Materialized Views: Refresh Statistics History

Materialized Views Refresh statistics can be collected in Oracle Database in varying degrees of granularity to provide historical data for analysis and reporting.

Storing historical Materialized Views Refresh statistics provides insight into how the materialized view ecosystem (or a single, specific materialized view) has evolved. This data provides unique insight, both for historical analysis as well as for diagnosis purposes.

Pluggable Databases Ease of Adoption

Heat Map and Automatic Data Optimization Support for CDBs

Heat Map and Automatic Data Optimization (ADO) now support multitenant container databases (CDBs).

Heat Map and ADO are now usable in databases that are consolidated using Oracle Multitenant, providing the benefits of automatic tracking of access patterns at the block and segment level, and automatic storage and compression tiering of data based on user-defined policies.