Pickleball spielen

You’ll see two New Features about Partitions covered here:  MOVE ONLINE (12cR1) and READ ONLY (12cR2)

[oracle@uhesse ~]$ sqlplus adam/adam@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 08:53:59 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 05 2017 08:41:13 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1					  NO
Q2					  NO
Q3					  NO
Q4					  NO

SQL> select partition_name,bytes/1024/1024 as mb from user_segments where partition_name like 'Q%';

PARTITION_	   MB
---------- ----------
Q1		   40
Q2		   47
Q3		   40
Q4		   46

The next command has 12cR1 enhancements: row store compress basic is new syntax for compress basic, having the same effect than the 11g syntax. The online clause is the major improvement here, since it allows that movement while other sessions work with DML statements on that partition:

SQL> alter table sales_range move partition q1 row store compress basic online;

Table altered.

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1	   BASIC			  NO
Q2					  NO
Q3					  NO
Q4					  NO

SQL> select partition_name,bytes/1024/1024 as mb from user_segments where partition_name like 'Q%';

PARTITION_	   MB
---------- ----------
Q1		   16
Q2		   47
Q3		   40
Q4		   46

The following command is a 12cR2 New Feature – Partitions can be made READ ONLY now, preventing DML and DDL statements against them:

SQL> alter table sales_range modify partition q1 read only;

Table altered.

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1	   BASIC			  YES
Q2					  NO
Q3					  NO
Q4					  NO

SQL> delete from sales_range where time_id=to_date('01.01.2016','dd.mm.yyyy');
delete from sales_range where time_id=to_date('01.01.2016','dd.mm.yyyy')
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


SQL> alter table sales_range drop partition q1;
alter table sales_range drop partition q1
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

To undo the above:

SQL> alter table sales_range modify partition q1 read write;

Table altered.

SQL> alter table sales_range move partition q1 nocompress online;

Table altered.

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1					  NO
Q2					  NO
Q3					  NO
Q4					  NO

SQL> select partition_name,bytes/1024/1024 as mb from user_segments where partition_name like 'Q%';

PARTITION_	   MB
---------- ----------
Q1		   40
Q2		   47
Q3		   40
Q4		   46

Instead of the shown basic compression, OLTP compression and Hybrid Columnar Compression (HCC) can also be done online. Could be a topic of another article to cover that new syntax 🙂

Watch me on YouTube demonstrating the above: