March 2, 2017

New features in Oracle Database 12c Release 2

New features in Oracle Database 12.2.0.1

Released on October, 2016

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.

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

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.

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.

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.