Oracle introduced the long-awaited Database 12c Release 2 earlier this year at Oracle Open World.

SQL and PL/SQL:

• Starting with Oracle Database 12c Release 2 (12.2), we can truncate the return string of LISTAGG function, to fit within the maximum length supported for the return data type and display a truncation literal to indicate that the return value was truncated.
• In Oracle12c Release2 (12.2.0), maximum length of identifier names for most types of database objects has been increased to 128 bytes/chars from 30 bytes/chars.

Database:

• When we create a tablespace, we can specify that all tables and indexes, or their partitions, created in a tablespace are compressed by default.
• During tablespace creation, we can specify the type of table compression using the DEFAULT keyword, followed by the table compression clause including the compression type. We can also specify the type of index compression using the DEFAULT keyword, followed by index compression clause and the index compression type.
• Release 2 incorporates significant support enhancements for storing and querying JavaScript Object Notation (JSON) documents for Oracle Database.
• Sharding: Sharding is an application-managed scaling technique using many independent databases. The data is split into multiple databases (called shards). Each database holds a subset of the data, this can be either be range or hash. We can also split the shards into multiple shards as the data volume or access to this data grows. Shards then can be replicated for availability and scalability reasons.
• We will have a Shard database with tablespace sets, and up to 1000 shards. DDL applied on one shared will be pushed to the others. Some common data can be duplicated.
• 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. Use the ALTER TABLE…MOVE statement to move a table to a new segment or tablespace.
• Password file is managed and transported via the Redo mechanism.

Materialized Views:

• A materialized view that uses the ON STATEMENT refresh mode is automatically refreshed every time a DML operation is performed on any of the materialized view’s base tables.
• A materialized view based on approximate queries uses SQL functions that return approximate functions in its defining query.
• 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.
• Oracle Database performs fast refresh for materialized views that are defined using approximate queries.
• Queries containing SQL functions that return approximate results are automatically rewritten to use a matching materialized view, if these queries can be answered using the materialized view.
• Real-time materialized views provide fresh data to user queries even when the materialized view is marked as stale. A real-time materialized view is a type of materialized view that provides fresh data to user queries even when the materialized view is not in sync with its base tables because of data changes.

Partitioning:

• 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.
• Automatic List-Partitioned Table – The automatic list partitioning method enables list partition creation on demand.

Data Guard:

• AWR reports are now available on Active Data Guard (ADG) standby databases.
• Alternate prioritization – we will be able to group some destinations and give them a priority over some other destinations. This enable us to decide what will happen when the main destination is back (failback).
• Multi-Instance Redo Apply, in parallel, multi-instance recovery – when standby is RAC, all of its instances will use the MRP0 process for applying redo.

Pluggable Databases:

• In Release 1, we can have 252 pluggable databases per managed database instance. From Release 2 onwards, we can have 4096 pluggable databases per managed database instance.
• PDB Refresh with one click.
• PDB Hot Cloning introduced – a running database cloned to create a new instance, without shutting it down or losing data.
• Tenant Relocation: Tenant (PDB) database can now be relocated online, then unplug/plug operation, which was introduced in 12c Release 1.
• Application Containers is introduced. Pluggable Databases share application objects, mostly code and metadata.
• Heat Map and Automatic Data Optimization (ADO) now support multitenant container databases (CDBs).
• Exadata In-Memory operations can now be run on ADG (Active Data Guard) standby databases.
• PDB Relocate with zero downtime Sharding (Horizontal scaling) for web applications.

New init parameters:

alter session set approx_for_aggregation = TRUE;

alter session set approx_for_count_distinct = TRUE;

New Views/Packages in Oracle 12c Release2:

ALL_ANALYTIC_VIEWS
ALL_ANALYTIC_VIEW_COLUMNS
ALL_ATTRIBUTE_DIM_ATTRS
ALL_HIERARCHIES
ALL_HIER_COLUMNS
ALL_ANALYTIC_VIEW_HIERS
DBMS_HIERARCHY

Some highlights new features are: 

• In Memory Database
  An ADG standby now runs with the primary database. Database queries are improved 40x, 60x (JSON). The new in-memory column format is moved in Exadata Flash Cache. Non-Volatile Memory is coming in 2018.
• Transforming to Big Data 
  Tools and storage are available for big data with fast SQL access. Big data guide can extract metadata out of the JSON documents. Comprehensive data science capabilities such as machine learning, graph, spatial and multimedia analysis are ready for use.
• Optimized for Cloud (Lower Cost, Agility, Elastics Scaling)
  Instead of running DB on commodity hardware, Oracle database is optimized for cloud hosting with multi-tenancy. The new sharding database is optimized for distributed database architecture.