New features in Oracle Database 12c Release 2

New features in Oracle Database 12.2.0

 Released in 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), the maximum length of identifier names for most types of database objects have 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 the 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.
• In-Memory Column Store (IM column store), in an optional area, in the SGA that stores whole tables, table partitions, individual columns, and materialized views in a compressed columnar format. The IM column store is a supplement to, rather than a replacement for, the database buffer cache.
• New views and supplied PL/SQL functions are used to determine the hostname of an outgoing database link, view detailed information about incoming database links, and determine the source of high system change number (SCN) activity for incoming database links.
• 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.
• From 12c, rows inserted with an array insert (along with direct-path INSERT) can be compressed using Hybrid Columnar Compression.
• New database resident connection pooling parameter (MAX_TXN_THINK_TIME) for long-running transactions.
• Password file is managed and transported via the Redo mechanism.
• Oracle Database can prespawn processes for better client connection performance.
• New command line tool - sqlcl, SQL Developer Command Line tool, which is better than SQL * Plus.
• READ object privilege and READ ANY TABLE system privilege - privilege on an object enables a user to select from an object without providing the user with any other privileges.
• Resource Manager can limit the amount of PGA memory that can be allocated to each session in a particular consumer group.
• In Oracle Database 12c Release 2 (12.2), tables with BFILE columns can be redefined online.
• Data Pump can keep, override, or drop the In-Memory clause for database objects being imported.
• To improve Oracle Database performance, DBAs can force an instance to store the database in the buffer cache.
• LOCAL_UNDO_ENABLED is TRUE, every container in the CDB uses local undo and each PDB must have its own local UNDO tablespace.
• Automatic Big Table Caching feature enables parallel queries to use the buffer cache.

Materialized Views:

• A materialized view, in Oracle 12c Database, 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 provide 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.
• We can refresh dependent FAST refreshable materialized views, during online table redefinition by setting the refresh_dep_mviews parameter to Y, in REDEF_TABLE or START_REDEF_TABLE procedure.

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.
• Partitioning for external tables provides fast query performance and enhanced data maintenance.

Data Guard:

• From 12c Oracle, AWR reports are available on Active Data Guard (ADG) standby databases.
• Alternate prioritization - we will be able to group some destinations and give them priority over some other destinations. This enables 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.
• Exadata In-Memory operations can now be run on ADG (Active Data Guard) standby databases.

Pluggable Databases:

• In 12c Release 1, we can have 252 pluggable databases per managed database instance. From 12c 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 were introduced. Pluggable Databases share application objects, mostly code and metadata.
• Heat Map and Automatic Data Optimization (ADO) now support multitenant container databases (CDBs).
• PDB Relocate with zero downtime Sharding (Horizontal scaling) for web applications.
• Save State - the state/mounted mode of PDB can be saved to avoid opening the database every time after shutdown. 
  SQL> alter pluggable database orclpdb save state;

New initialization parameters in Oracle Database 12c Release 2 :

• alter session set approx_for_aggregation = TRUE;
• alter session set approx_for_count_distinct = TRUE;
• local_undo_enabled = true/false
• INSTANCE_ABORT_DELAY_TIME initialization parameter specifies a delay time when an error causes an instance to abort.

New Commands:
SQL> alter pluggable database orclpdb save state;
SQL> alter database local undo on/off;


New Views in Oracle 12c Release2:

• ALL_ANALYTIC_VIEWS
• ALL_ANALYTIC_VIEW_COLUMNS
• ALL_ATTRIBUTE_DIM_ATTRS
• ALL_HIERARCHIES
• ALL_HIER_COLUMNS
• ALL_ANALYTIC_VIEW_HIERS

New Packages in Oracle 12c Database Release 2:

• DBMS_HIERARCHY
• DBMS_JSON
• DBMS_HADOOP
• DBMS_GOLDENGATE_ADM
• DBMS_HANG_MANAGER
• DBMS_JAVASCRIPT
• DBMS_PROCESS
• DBMS_TNS

Related Oracle Articles: What's New in Oracle 12c Release 1      Oracle Database 18c new features