This year, Oracle Open World revealed the much awaited Oracle 12.2 (or 12cR2) version. Unfortunately for on-prem Oracle customers it will be available first in the cloud now (for Exadata Express Cloud Service) and in November for the rest of the Cloud services. That will allow Oracle itself to “maintain” the release and also squash some final bugs before deliver to your datacenter.

Still, Oracle published some sort of Oracle Database 12cR2 documentation revealing some interesting features. Here we will focus on Core Oracle Partitioning features delivered by this version.

• Read-only partitions – That will allow the DBAs to restrict DML on partitions and sub-partitions. It will add to the feature introduced in Oracle 11gR1: Read Only Tables. It is now possible to decide that, for example, very old data in a large partitioned table should not be subject to any DML.
  The definition is based on a keyword READ ONLY or READ WRITE (default) that is defined at DDL level when creating the partitioned table. Here is a small example:


CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) READ WRITE
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2016 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')) READ ONLY
TABLESPACE tsa
, PARTITION sales_q2_2016 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy'))
TABLESPACE tsc
, PARTITION sales_q4_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy'))
TABLESPACE tsd
);


• Online conversion of non-partitioned to partitioned table – Before this, DBAs had to deal with DBMS_REDEFINITION to ensure that they can do it online, without compromising the service availability. That worked for several years, but DBMS_REDEFINITION requires some preparation and some attention to some details (example: PK or rowid redefinition, table object copy, etc).
  It is now possible to do it with a simple ALTER TABLE:


ALTER TABLE t1 MODIFY
PARTITION BY RANGE (amount) INTERVAL (100)
( PARTITION P1 VALUES LESS THAN (300),
PARTITION P2 VALUES LESS THAN (700)
) ONLINE
UPDATE INDEXES
( IDX01_AMOUNT LOCAL
( PARTITION IP1 VALUES LESS THAN (MAXVALUE))
);


• Filtering Partition Operations (Maintenance) – This one is very clever. You can now data filtering some maintenance operations. By example is easier to understand. You moved all the transactions from 2015 from VISA to a new compressed state into a new archive tablespace..online!


ALTER TABLE move_transactions
MOVE PARTITION trx_2015 TABLESPACE archived COMPRESS ONLINE
INCLUDING ROWS WHERE trx_owner = 'VISA';


• Automatic and multi-column LIST partitioning – Similar to single list partitioning, it is now possible to create list partitions based on several columns with PARTITION BY LIST (column1, column2).
  Also, finally (!) it is possible to automatic create list partitions with values from a specific column, even if you don’t add the value literals for the list partitions. A keyword AUTOMATIC is added.


*Oracle Example:
CREATE TABLE q1_sales_by_region
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state) AUTOMATIC
(PARTITION q1_northwest VALUES ('OR', 'WA'));


• Create a table for exchange with a partitioned table. Easy as the following. Another useful resource for any DBA:


CREATE TABLE sales_exchange
TABLESPACE my_sales_tblspace
FOR EXCHANGE WITH TABLE sales;


That are some other number of small improvements in Partitioning in R2 mainly in splitting partitions, online operations, etc, but the essential is here.
Enjoy your (for now) cloud 12.2 🙂