Oracle Database 12c Release 2: What's New, Release History & Support Lifecycle

What Is New in Oracle Database 12c Release 2

What Changed in Oracle Multitenant (PDB) Architecture in 12c Release 2?

Oracle Database 12c Release 2 delivers the most significant expansion of the Multitenant architecture since its introduction, making PDBs production-ready at enterprise scale. The headline number is support for up to 4,096 pluggable databases per CDB -- a jump that fundamentally changes how large organizations can consolidate workloads.

For day-to-day operations, the feature that matters most to most DBAs is Near Zero Downtime PDB Relocation. You can now move a PDB from one CDB to another while it remains fully open and serving application connections. The source PDB continues to forward sessions transparently until the cutover completes. In practice, this collapses a maintenance window that used to take hours into a few seconds of redirect latency.

Other Multitenant improvements that directly reduce operational overhead include:

• PDB Refresh -- a read-only clone PDB that can refresh itself from a source PDB on demand or at a scheduled interval, useful for reporting environments that need near-current data without impacting production.
• PDB Archive Files (.pdb files) -- pack an entire PDB into a single compressed archive for transport, similar to an AWR snapshot but for the whole database.
• Flashback Pluggable Database -- flash back an individual PDB to a restore point without touching other PDBs in the same CDB. Previously, Flashback Database operated only at the CDB level.
• Per-PDB I/O Rate Limits and Memory Resource Management -- enforce resource isolation between tenants with finer-grained controls, critical when mixing OLTP and DSS workloads in a shared CDB.
• Application Root -- a new container type that allows you to define a master application schema and patch or upgrade it once, propagating changes automatically to all application PDBs. This is the right tool for SaaS deployments where many tenants share the same schema version.
• AWR support at the PDB level -- each PDB can now maintain its own AWR snapshots, giving you workload history per tenant rather than only at the CDB level.

Watch out for the character set change: each PDB can now have its own character set, and Logical Standby and LogMiner both support CDBs with PDBs using different character sets. If you rely on cross-PDB queries, validate collation behavior in your test environment before promoting this configuration to production.

-- Relocate a PDB online with near-zero downtime
ALTER PLUGGABLE DATABASE pdb_sales
  RELOCATE TO cdb_prod
  AVAILABILITY MAX;

-- Create a refreshable clone PDB
CREATE PLUGGABLE DATABASE pdb_reporting
  FROM pdb_sales@dblink_sales
  REFRESH MODE EVERY 60 MINUTES;

How Does Oracle Database 12.2 Improve In-Memory Performance?

Oracle Database In-Memory received a substantial set of enhancements in 12.2 that close gaps which limited its usefulness in earlier releases. The most operationally significant are In-Memory FastStart, Join Groups, and In-Memory Expressions.

In-Memory FastStart solves the cold-start problem that has troubled teams running large In-Memory column stores since 12.1. Previously, after a database restart, the entire column store had to be re-populated from disk, which could take many minutes on large configurations. FastStart persists the columnar format directly to an ADO-managed tablespace on disk so that re-population on startup is dramatically faster. Most teams running In-Memory with 50 GB or more of column store data will see this as an immediate operational win.

Join Groups allow you to declare that two columns from different tables are logically related and will be joined frequently. The database uses this metadata to eliminate dictionary decompression during joins inside the column store, which can substantially improve join performance on high-cardinality columns. In practice, define join groups on your most-executed fact-to-dimension joins in analytics workloads.

Additional In-Memory improvements include:

• In-Memory Expressions -- the database can automatically identify frequently-evaluated expressions (virtual columns, computed aggregates) and materialize them in the column store, reducing repeated CPU work during scans.
• In-Memory Virtual Columns -- virtual column definitions can now be explicitly loaded into the IM column store.
• Dynamic Resizing -- the In-Memory area can be resized online without a database restart.
• Active Data Guard In-Memory -- the IM column store is now supported on Active Data Guard standbys, allowing read-heavy analytics to run on the standby without consuming primary resources.
• Automatic Data Optimization (ADO) integration -- ILM policies can manage which segments are kept in the column store, automating eviction of cold data.

-- Create a join group to accelerate fact-dimension joins
CREATE MEMOPTIMIZE FOR READ ON sales (prod_id);

CREATE JOIN GROUP jg_sales_prod (
  sales(prod_id),
  products(prod_id)
);

-- Verify IM expression tracking
SELECT * FROM V$IM_EXPRESSIONS_STATS;

What Are the New Security Capabilities in Oracle Database 12.2?

Oracle 12.2 tightens the default security posture of a newly provisioned database in ways that can surprise teams upgrading from older releases. Strong password verifiers are now required by default -- the legacy 10G hash is no longer generated for new accounts, and the database enforces this at the profile level. If your applications use JDBC thin drivers or OCI clients that rely on the older authentication protocol, test connectivity carefully before upgrading production.

TDE Tablespace Live Conversion is arguably the most operationally impactful encryption feature in this release. In prior versions, encrypting an existing tablespace required taking it offline. Now you can convert an unencrypted tablespace to TDE online while it remains fully read-write. For shops that have been deferring TDE adoption because of the maintenance window cost, this removes the primary blocker.

Key additional security changes include:

• Fully Encrypted Database -- a new CREATE DATABASE option encrypts all tablespaces, including SYSTEM, SYSAUX, UNDO, and TEMP, at creation time. This is the recommended path for new cloud deployments.
• Support for ARIA, SEED, and GOST algorithms in TDE -- adds South Korean and Russian government-mandated encryption standards, relevant for regulated deployments in those markets.
• Privilege Analysis enhancements -- the results comparison feature lets you diff two privilege analysis runs, making it easier to verify least-privilege remediation over time.
• Role-Based Conditional Auditing -- audit policies can now fire conditionally based on the roles active in the session, reducing audit volume while retaining coverage where it matters.
• Automatic Locking of Inactive User Accounts -- user accounts that have not been used for a configurable period are automatically locked, reducing the attack surface from dormant accounts.
• Inherit Remote Privileges -- a new privilege controls whether an invoker-rights unit running over a database link inherits the caller's remote privileges. This matters if you have cross-schema or cross-database PL/SQL chains; review them before upgrading.
• Oracle Database Vault Simulation Mode -- test realm and command rule configurations without actually blocking access, significantly lowering the risk of a misconfigured Vault deployment causing an outage.

How Does Oracle Sharding Work and Who Should Use It in 12.2?

Oracle Database Sharding is a brand-new horizontally partitioned architecture introduced in 12.2 that distributes a single logical database across multiple independent physical databases, each called a shard. Unlike traditional partitioning within a single instance, sharding allows each shard to run on completely separate hardware, providing near-linear scalability for OLTP workloads that are naturally partitioned by a key such as customer ID or tenant ID.

This matters if you are building systems where a single RAC cluster is not sufficient -- high-volume internet-scale OLTP, global SaaS platforms, or telco charging applications. Sharding is not a replacement for partitioning or RAC for general use; it is a specialized architecture that requires careful schema design around a shard key.

Key sharding capabilities in this release:

• System-Managed and User-Defined Sharding -- the database can automatically distribute chunks across shards (consistent hash), or you can define a custom mapping.
• Data-Dependent Routing (DDR) -- the connection pool (Universal Connection Pool or JDBC) automatically routes application queries to the correct shard based on the shard key, with no application changes required beyond providing the key at connect time.
• Cross-Shard Queries -- a shard catalog coordinator can execute queries that span multiple shards using statement-level routing, useful for reporting that aggregates across all tenants.
• Integrated Data Guard per shard -- each shard automatically gets a Data Guard standby using DBCA, so shard-level HA is built into the provisioning workflow.
• Shard Director -- a Global Service Manager component that provides lightweight routing and connection balancing across the shard fleet.

In practice, teams evaluating sharding should start with the shard catalog and a two-shard test configuration to understand the schema constraints (no foreign keys across shards, no sequences spanning shards) before committing to this architecture.

What SQL and PL/SQL Developer Improvements Come With Oracle 12.2?

Oracle 12.2 delivers a meaningful set of SQL and PL/SQL improvements that reduce boilerplate code and improve developer productivity. These are changes that affect the code you write every day rather than just infrastructure behavior.

Long Identifiers is the change with the broadest impact: the maximum length of object names -- tables, columns, indexes, packages, and most other identifiers -- increases from 30 characters to 128 characters. This single change removes a major friction point when migrating applications from SQL Server, PostgreSQL, or DB2, where longer names are standard. Note that some data dictionary views and legacy tools may not display the full width; check your monitoring and ETL tooling after upgrading.

Other SQL and PL/SQL improvements worth knowing:

• VALIDATE_CONVERSION function -- returns 1 if an expression can be safely converted to a target data type, 0 otherwise. Replaces the common workaround of using CAST inside an exception handler to detect bad data.
• CAST with DEFAULT ON CONVERSION ERROR -- allows you to specify a fallback value when a type conversion fails, keeping a query alive even when source data is dirty.
• Real-Time Materialized Views -- if a materialized view is stale (not yet refreshed), the query rewrite engine can now apply a delta computed from the MV log on the fly, serving a fresh result without waiting for a full refresh. This dramatically changes the trade-off between refresh frequency and query accuracy.
• LISTAGG with DISTINCT -- eliminates duplicates from the aggregated list, removing a common workaround involving subqueries or analytic deduplication before the LISTAGG call.
• DBMS_PLSQL_CODE_COVERAGE -- a new package that instruments PL/SQL unit tests to report which lines were executed, enabling coverage-driven testing without third-party tools.
• PL/SQL DEPRECATE pragma -- marks a package member, type, or subprogram as deprecated so that the compiler emits a warning (PLW-6019) when callers reference it. Useful for managing API evolution in shared libraries.
• Approximate Query Processing -- APPROX_COUNT_DISTINCT and related functions now have broader optimizer integration, useful for analytics dashboards that can trade a small percentage of accuracy for much faster response.
• Auto-List Partitioning -- a new partition strategy where new list values automatically create partitions on first insert, eliminating the DBA intervention required when an unknown value arrives.

-- VALIDATE_CONVERSION: safe conversion check
SELECT order_id,
       VALIDATE_CONVERSION(amount_raw AS NUMBER) AS is_valid_number
FROM staging_orders;

-- CAST with fallback for dirty data
SELECT CAST(amount_raw AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS amount
FROM staging_orders;

-- Real-Time Materialized View query (fresh result without refresh)
SELECT /*+ REWRITE */ product_id, SUM(qty)
FROM mv_daily_sales
GROUP BY product_id;  -- Uses on-query computation from MV log if stale

-- Auto-List Partitioning example
CREATE TABLE orders (
  region VARCHAR2(30),
  order_date DATE,
  amount NUMBER
)
PARTITION BY LIST (region) AUTOMATIC (
  PARTITION p_east VALUES ('EAST'),
  PARTITION p_west VALUES ('WEST')
);
-- A new region value ('CENTRAL') auto-creates p_central on first insert