Top 15 new features of Oracle Database 12.2 for developers

Oracle Database 12c Release 2 in now available. With this post I am trying to compile top 15 new features of Oracle Database 12.2 for developers. You can check Oracle Database 12c New Features for Developers here (in comparison of Oracle 11g).

1) Long Identifiers: The maximum length of identifiers is increased to 128 bytes from 30 bytes. It gives you greater flexibility in defining longer and more expressive table (and other objects) names.

2) LISTAGG Functionality Enhanced: LISTAGG aggregates the values of a column by concatenating them into a single string. LISTAGG function has been improved to manage situations where the length of the concatenated string is too long. The new option, overflow truncated, adds three dots at the end of the concatenated string followed by the number of truncated characters in brackets.

3) CAST Function Enhanced With Error Handling: CAST function is enhanced to return a user-specified value instead of raising an error, when the conversion is not valid.

4) Auto-List Partitioning: With Auto-List Partitioning, database automatically creates a new partition for every distinct partition key value. It removes the management burden to manually maintain a list of partitions for a large number of distinct key values that require individual partitions. It also automatically deals with the unplanned partition key values without the need of a DEFAULT partition.

5) With Oracle 12c R2 Non-partitioned tables can now be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. We can also Online SPLIT Partition and Sub-partition.

6) Multi-Column List Partitioning: List partitioning functionality is expanded to allow multiple partition key columns.

7) Read-Only Partitions: Partitions and sub-partitions can be individually set to a read-only state.

8) Materialized Views - Delta Computation: Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables. Using materialized view logs for delta computation with stale materialized view, and return correct results in real time.

9) Materialized Views: Statement-Level Refresh: Materialized join views can now be refreshed when a DML operation takes place, without the need to commit. It offers built-in refresh capabilities that avoids the need of customer-written trigger-based solutions.

10) VALIDATE_CONVERSION Function: New SQL function, VALIDATE_CONVERSION, determines whether a given input value can be converted to the requested data type.

11) Approximate Query Processing: Approximate query aggregation is a common requirement now a days for data analysis. The approximate processing of large volumes of data is significantly faster than the exact aggregation.

12) JSON searching Improvements: We can now generate JSON documents directly from SQL queries. The JSON search index supports RANGE and LIST partitioned tables and support range-based searching on numeric values. JSON documents can be manipulated using PL/SQL now.

13) High level of index compression: it provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the various advantages over low compression.

14) Index Monitoring: With 12.2 Index monitoring is now enabled by default, tracks the usage at execution level and keeps a lot of information

15) External Tables – partitions: External tables can now be partitioned by using a set of files (instead of the single one), each representing a table partition.

Apart from above new features, As per the Oracle Documentation, The non-CDB architecture is deprecated in Oracle Database 12c, and may be desupported and unavailable in a release after Oracle Database 12c Release 2. Oracle recommends use of the CDB architecture.

Update 03-Mar-2017:
Oracle Database EE 12.2 (on Premises) is now available. Earlier it only available on Oracle Cloud.

Following is the link for New Features of Oracle Database 12.2.

But the main improvement area (or you may say issue resolved) in Oracle 12.2 from 12.1 is, Oracle has improved a lot on “Adaptive Query Optimization”, which has given a lot of trouble (in my views) to DBAs and Developers in Oracle 12c R1.

Related Posts:
- Oracle Database 12c New Features for Developers
- JSON in Oracle 12c Database with Examples
- New Features for Developers in Oracle 11g
- Sequence Performance Enhancement with each Oracle Version
- Fill Gaps in Sparse Data - Partitioned Outer Join
- SQL Interview Question Answers
- Complex View Merging Transformation and ORA-00979 in Oracle 12c


  1. Are listagg length changes specific to 12.2? What is the new limit

    1. Oh they seem to have added options to handle the overflow

    2. Yes, Overflow truncated :) But as LISTAGG supports varchar2, and we can extend the size of varchar2 to 32767 in Oracle 12c by setting MAX_STRING_SIZE initialization parameter to EXTENDED

    3. are there any drawbacks with extending it?

    4. I have heard about some implications on indexes with extended data type and it cause chained rows which may hit performance. Also we need to test it for how listagg behaves with extended datatypes.

    5. It's a shame LISTAGG update didn't come with DISTINCT option

  2. The non-CDB architecture is NOT de-supported in 12.2. De-supported means removed and as you said, it still exists.

    1. Blog Post Updated with Link to Oracle Documentation.

  3. Is the release 12.2 had been released

  4. You might think about leaving "for developers" off your titles. Many of these features are more appropriate for DBAs...

    1. You could argue some categorisations, lines are blurred, but I see a lot of developer features. I think "Design" bucket are tools that could be considered by both, but not distinctly DBA.

      Design 1, 4, 6, 8, 15
      Develop 2, 3, 9, 10, 11, 12, (14)
      DBA 5, 7, 13, 14

    2. Question of perspective. All of the above features, except for maybe point 14, are features we would have to take care of ourselves as developers (at least in my company), without the intervention of the DBA team. So, I guess it depends on your company and situation.

    3. For me every thing comes under developer profile except installation, backup & recovery and related stuff :)

    4. That is a valid point. Many of the features never come up in day to day developer activity.

  5. They look like some great features. I think the materalized view enhancements will be the one I get the most out of. And of course the long identifiers! 30 characters was very small!

  6. 12 Things Developers Will Love About Oracle Database 12c Release 2 by Chirs Saxon (AskTom)

  7. Hi all,
    12.2 brings also some interesting improvements in PL/SQL-only data types binding using the DBMS_SQL API. With the new procedure DBMS_SQL.BIND_VARIABLE_PKG you can use binding with associative array indexed by PLS_INTEGER and RECORD type.