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

New features in Oracle Database 12c Release 1

Oracle Database 12c, c for cloud, 12.1.0, a multi-tenant database management system, with nearly 500 new features.

Released on June 26, 2013

SQL:

• Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K  (from 4K). MAX_STRING_SIZE = EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c.
• We can make a column invisible. 
  SQL> create table test (column-name column-type invisible);
  SQL> alter table table-name modify column-name invisible; 
  SQL> alter table table-name modify column-name visible;
• We can drop an index online.
  SQL> DROP INDEX ... ONLINE.
• We can drop a constraint online.
  SQL> DROP CONSTRAINT ... ONLINE.
• We can set unused columns online
  SQL> alter table table-name ... SET UNUSED COLUMN ONLINE.
• We can make an index visible or invisible to Optimizer.
  SQL> ALTER INDEX ... VISIBLE / INVISIBLE .
• Oracle Database 12c has a new feature called "Identity Columns" which are auto-incremented at the time of insertion (like in MySQL). 
  SQL> create table dept (dept_id number generated as identity, dept_name varchar);
  SQL> create table dept (dept_id number generated as identity (start with 1 increment by 1 cache 20 noorder), dept_name varchar);
• Temporary undo (for global temporary tables) will not generate undo. We can manage this by using initialization parameter temp_undo_enabled (=false|true).
• Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we'll get an error. In some cases, we might want two different types of index on the same data (such as in a Datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).
• PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one-shot function)
• The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types, etc...
• Pagination query, SQL keywords to limit the number of records to be displayed, and to replace ROWNUM records. 
  SQL> select ... fetch first n rows only; 
  SQL> select ... offset m rows fetch next n rows only; 
  SQL> select ... fetch first n percent rows only; 
  SQL> select ... fetch first n percent rows with ties;
• Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
  SQL> alter database move datafile 'path' to 'new_path';
• The TRUNCATE command has been enhanced with a CASCADE option which follows child records.
• dbms_sql.return_result returns formatted results in SQLPLUS.
• Introduction of audit policies and unified audit trail simplifies the configuration of database auditing in Oracle 12c.
• Reduces contents of regular UNDO, allowing better flashback operations.

PL/SQL:

• PL/SQL Unit Security - A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code.
• SQL WITH clause enhancement - In Oracle 12c, we can declare PL/SQL functions in the WITH clause of a select statement.
• Implicit Result Sets - create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch-up to other databases).
• MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database.
• PL/SQL white lists using the ACCESSIBLE BY clause, in Oracle Database 12c Release 1 (12.1.0).
• We can use Booleans values in dynamic PL/SQL. Still no Booleans as database type.

Database:

• New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.
• RESOURCE role does not include UNLIMITED TABLESPACE anymore.
• No need to shutdown database for changing archive log mode.
• Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
  sysbackup for Backup operations
  sysdg for Data Guard operations
  syskm for Key management
• Like sys, system, we have new default SYS users, in Oracle 12c Release1.
  sysbackup for Backup operations
  sysdg for Data Guard operations
  syskm for Key management
• Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
  $ impdp ... transform=disable_archive_logging:y
• expdp has transport view, view_as_tables options.
  $ expdp ... VIEWS_AS_TABLES=test_view
• New ENCRYPTION_PWD_PROMPT parameter allows you to specify whether Data Pump should prompt you for the encryption password, rather than you entering it on the command line.
• Enhanced statistics --- frequency histograms can be created up to 2048 distinct values (increased from 254 distinct values), dynamic sampling up to eleven, and stats automatically gathered during load.
• Two new types of histograms were introduced --- top-frequency histograms and hybrid histograms.
• Row pattern matching - "MATCH_RECOGNIZATION" (identification of patterns within a table ordered/sorted by the SQL statement).
• Adaptive execution plans (change of the plan at runtime).
• Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
• Dynamic sampling has been renamed to Dynamic Statistics in Oracle 12c release 1.
• Multi-threaded database with parameter threaded_executions. Multiple processes and multiple threads within each process, provides improved performance and manageability through more efficient sharing of system and processor resources.
• Oracle introduced parameter PGA_AGGREGATE_LIMIT which is a real/PGA memory limit.
• UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace. Queried through v$tempundostat.
• Oracle Enterprise Manager Express (lightweight EM Cloud Control 12c version), replaces the Oracle Database console and, is installed automatically.
• enable_ddl_logging - when the logging of DDL statements is enabled, DDL statements are recorded in a separate DDL log (instead of the alert log file).
• Some information, that can be used to debug a problem, is recorded in a separate debug log instead of the alert log.
• Oracle Caching mode: It enables to force caching of all segments into buffer cache automatically(when the segments are accessed).
  ALTER DATABASE FORCE FULL DATABASE CACHING;
• Database Smart Flash Cache support for multiple flash devices - A database instance can access and combine multiple flash devices for Database Smart Flash Cache without requiring a volume manager.
• New type of sequences - session sequences, for global temporary tables
• Materialized View out-of-place refresh.
  DBMS_MVIEW.REFRESH('TEST_MV', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);
• CSSCAN (Character Set Scanner) and CSALTER utilities were replaced with DMU (Database Migration Assistant for Unicode).
• Monitor the privilege assignments easily in 12c Oracle with DBMS_PRIVILEGE_CAPTURE.
• SELECT ANY DICTIONARY no longer provides access to tables with password hashes (USER$ etc).
• Lateral Views - a lateral view is a view that references columns from a table that is not inside the view.
• Reduces the size of redo associated with recovering the regular UNDO tablespace.

ASM: (Oracle Grid Infrastructure new features)

• Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, wherein 12c the ability to get the extent map from remote ASM instance makes the node useful.
• 12.1.0.2 introduced a new ASM feature that might prevent us from adding dissimilar size disks into normal or high redundancy disk groups.
• Introduction of Flex Cluster, with lightweight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don't require any network heartbeat.

RMAN:

• Table (or table partition) Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required). The table is recovered into an auxiliary instance and there is the option to import the recovered table into a new table or partition using REMAP option or create the expdp dump of the recovered table only, for import at a later time of your choosing.
  RMAN> recover table table_name until scn scn_number auxiliary destination on 'path';
  RMAN> recover table sh:sales:P1 remap table sh.sales:P1:new_tab
• Running SQL commands and executing PLSQL procedures in RMAN without SQL keyword.
  RMAN> select * from v$session;
• Recover or copy files from Standby databases. 
  Refresh a single datafile on the primary from the standby (or standby from primary).
• Multi-sectional backup functionality, to improve backup and restore operation of large size data files, extended further in 12c to supports image copies and incremental backups along with the full database backupsets.
• Table level restoration i.e. object level.
• Incremental recovery more faster, many of the tasks removed.
• In Oracle 12c, ACTIVE DUPLICATE DATABASE does not require any pre backup existence to clone the database, RMAN first takes backup of datafiles into backupsets, and transmits to the auxiliary location and will be restored/recovered subsequently.
• New NOOPEN clause in 12c ACTIVE DUPLICATE DATABASE, will prevent opening database immediately after the cloning process completion and the database will remain in MOUNT state and we have to manually open the database.
• Rolling forward/Synchronizing a standby database.

Partitioning:

• Partitioning enhancements (partition truncate, cascading, global index clean up, online moving of a partition, ...)
• SQL> ALTER TABLE ... MOVE PARTITION ... ONLINE...
• Multiple partition operations in a single DDL.
• Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.
• Cascade for TRUNCATE and EXCHANGE partition.
• Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index clean-up happens later.
• Online move of a partition(without DBMS_REDEFINTIION) - DML operations can continue to run uninterrupted on a partition or subpartition that is being moved without using online table redefinition.
• To minimize downtime when redefining multiple partitions in a table, DBAs can redefine these partitions online in a single session.

Patching:

• Centralised patching.
• We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package
  SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
• We can test patches on database copies, rolling patches out centrally once testing is complete.

Compression:

• Automated compression with heat map.
• Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.
  Advanced Row compression (for Hot Data).
  Columnar Query compression (for Warm Data).
  Columnar Archive compression (for Archive Data).
• New parameters, SQLNET_COMPRESSION and SQLNET.COMPRESSION_SCHEME_LIST, allow the compression of data transitioning over Oracle Net Services between client and server. Compression can be enabled at the: Connection level (connect string, URL), Service level (tnsnames.ora, ldap.ora) and Database level (sqlnet.ora).

Data Guard:

• Oracle Database 12c introduces a new redo transportation method that omits the acknowledgment (to primary) of the transaction on standby. This feature is called "Fast Sync" redo transport.
• Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.
• Data Guard Broker commands have been extended. The "validate database" command checks whether the database is ready for role transition or not.
• In 12c it is possible to cascade a Standby Database in Real Time, that is, the first Standby Database can send Redo from the Standby RedoLogs to the cascaded Standby Database.
• Data Guard Broker now supports cascaded standby.
• In 12c Data Guard, if you want to start MRP in RTA (real-time apply), use
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
• In 12c Data Guard, if you want to start MRP in non RTA (real-time apply), use ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
  
• Global data services, transparent to failover / switchover no client reconfiguration required.
• Data Guard supports Physical Standby Database for a Multitenant Database.
• Global Temporary Tables can now be used on standby databases/Active Data Guard (ADG) standby databases. Can perform DML on global temporary tables, on standby databases, when temp_undo_enabled=true.

OEM (Oracle Enterprise Manager):

• Oracle Enterprise Manager Database Express (DB Express), also referred to as EM Express, is a web-based tool for managing Oracle Database 12c, to support basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning.
• EM Express is similar to DB Control (Oracle Enterprise Manager DB Console) in 10g & 11g, but not (full) replacement because EM Express has very few features.
• To use EM Express, a database user needs to have EM_EXPRESS_BASIC or EM_EXPRESS_ALL role. DBA role includes both the EM_EXPRESS_BASIC and the EM_EXPRESS_ALL roles. 
  SQL> grant EM_EXPRESS_BASIC to user;
  SQL> grant EM_EXPRESS_ALL to user;

Pluggable Databases:
In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.

Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation.
Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.

A new admin role "CDB Administrator" has been introduced in Oracle 12.1.0 release databases.
Multiple LGWR processes for each PDB’s and multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.

All Oracle database options/features are available on the PDB level.

RMAN backup at CDB level.

We can unplug a PDB from a CDB to another CDB.
PDB's can be cloned inside the CDB.
Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a "PDB Seed".
Database patch/upgrade management very quick as CDB is a single point of installation.
Each PDB has its own data dictionary.
Data Guard configuration on CDB as a whole.
RMAN point-in-time recovery at PDB level (while other PDB's remain open).
Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.
Flashback of a PDB should be available for Oracle 12c Release 2.

Entire containers can be backed up in a single run, regardless of how many databases they contain.
Upgrade one container database and all pluggable databases are upgraded.

New Commands in 12c

• create pluggable database ...
• alter pluggable database ...
• drop pluggable database ...
• alter session set container = container_name;
• alter system set parameter=value container=current | ALL;

New initialization parameters in Oracle 12c Release 1

• temp_undo_enabled (=false | true)
• DB_FLASH_CACHE_FILE
• DB_FLASH_CACHE_SIZE
• enable_pluggable_database (=false | true)

New tables/views in Oracle Database 12c

• cdb_***     views
• dba_pdbs
• v$pdbs
• v$containers
• v$io_outlier
• v$lgwrio_outlier
• v$kernel_io_outlier

New Packages in 12c Oracle Release 1

• dbms_pdb
• dbms_qopatch
• UTL_CALLSTACK
• dbms_redact
• dbms_rolling
• DBMS_CREDENTIAL
• DBMS_HEAT_MAP
• DBMS_LOG
• DBMS_SCN

New features in Oracle Database 12c Release 2

Related Oracle Articles: New features in Oracle Clusterware 12c  Oracle Database 18c new features