Read-Only partitions and subpartitions in OracleDatabase12c 2nd (12.2,

Read-Only partitions and subpartitions in OracleDatabase12c 2nd (12.2,

Starting from Oracle Database 12c 2nd (12.2), you can mark partitions and subpartitions as read-only to protect their data from unexpected changes.

You can use a READ-ONLY partition to CREATE a partition TABLE by specifying the READ-ONLY clause at the partition level of the create table statement.

The following is an experiment:

1. Read-Only Partition

create table t1 (  id            number,  code          varchar2(10),  description   varchar2(50),  created_date  date,  constraint t1_pk primary key (id))partition by range (created_date)(  partition t1_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),  partition t1_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),  partition t1_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')) read only  );SQL> set linesize 100SQL> column table_name format a30SQL> column partition_name format a30SQL> column read_only format a9SQL> select table_name, partition_name,read_only from user_tab_partitions where  table_name = 'T1' order by 1, 2;TABLE_NAME       PARTITION_NAME      READ_ONLY------------------------------ ------------------------------ ---------T1       T1_2016      NOT1       T1_2017      NOT1       T1_2018      YES

Alternatively, a table can be created as read-only, and some partitions are marked as read/write.

SQL> drop table t1 purge;create table t1 (  id            number,  code          varchar2(10),  description   varchar2(50),  created_date  date,  constraint t1_pk primary key (id))read onlypartition by range (created_date)(  partition t1_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),  partition t1_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),  partition t1_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')) read write);SQL> set linesize 100SQL> column table_name format a30SQL> column partition_name format a30SQL> column read_only format a9SQL> select table_name, partition_name,read_only from user_tab_partitions where  table_name = 'T1' order by 1, 2;TABLE_NAME       PARTITION_NAME      READ_ONLY------------------------------ ------------------------------ ---------T1       T1_2016      YEST1       T1_2017      YEST1       T1_2018      NO

You can use the alter table statement to change the partition status.

QL> alter table t1 modify partition t1_2016 read write;Table altered.SQL> alter table t1 modify partition t1_2017 read write;Table altered.SQL> alter table t1 modify partition t1_2018 read only;Table altered.SQL> select table_name, partition_name,read_only from user_tab_partitions where  table_name = 'T1' order by 1, 2;TABLE_NAME       PARTITION_NAME      READ_ONLY------------------------------ ------------------------------ ---------T1       T1_2016      NOT1       T1_2017      NOT1       T1_2018      YES

An error occurs when you try to insert a read-only partition.

SQL> insert into t1 values (1, 'ONE', 'Description for ONE', sysdate);insert into t1 values (1, 'ONE', 'Description for ONE', sysdate)            *ERROR at line 1:ORA-14466: Data in a read-only partition or subpartition cannot be modified.

2. READ-ONLY subpartitions by specifying the read only clause at the subpartition level of the create table statement, you can use READ-ONLY subpartitions to CREATE a sub-partition TABLE

SQL> drop table t1 purge;create table t1 (  id            number,  code          varchar2(10),  description   varchar2(50),  created_date  date,  constraint t1_pk primary key (id))partition by list (code)subpartition by range (created_date) (  partition part_gbr values ('GBR') (    subpartition subpart_gbr_2016 values less than (to_date('01-JUL-2017', 'DD-MON-YYYY')) read only,    subpartition subpart_gbr_2017 values less than (to_date('01-JUL-2018', 'DD-MON-YYYY')),    subpartition subpart_gbr_2018 values less than (to_date('01-JUL-2019', 'DD-MON-YYYY'))  ),  partition part_ire values ('IRE') (    subpartition subpart_ire_2016 values less than (to_date('01-JUL-2017', 'DD-MON-YYYY')) read only,    subpartition subpart_ire_2017 values less than (to_date('01-JUL-2018', 'DD-MON-YYYY')),    subpartition subpart_ire_2018 values less than (to_date('01-JUL-2019', 'DD-MON-YYYY'))  ));SQL> set linesize 120SQL> column table_name format a20SQL> column partition_name format a20SQL> column subpartition_name format a20SQL> column read_only format a9SQL> select table_name, partition_name,subpartition_name,read_only from user_tab_subpartitions where  table_name = 'T1' order by 1, 2;TABLE_NAME     PARTITION_NAME  SUBPARTITION_NAME    READ_ONLY-------------------- -------------------- -------------------- ---------T1     PART_GBR  SUBPART_GBR_2016     YEST1     PART_GBR  SUBPART_GBR_2017     NOT1     PART_GBR  SUBPART_GBR_2018     NOT1     PART_IRE  SUBPART_IRE_2016     YEST1     PART_IRE  SUBPART_IRE_2017     NOT1     PART_IRE  SUBPART_IRE_2018     NO6 rows selected.

Alternatively, you can create a table or partition as read-only, and some sub-partitions are marked as read/write.

SQL> drop table t1 purge;create table t1 (  id            number,  code          varchar2(10),  description   varchar2(50),  created_date  date,  constraint t1_pk primary key (id))read onlypartition by list (code)subpartition by range (created_date) (  partition part_gbr values ('GBR') read write (    subpartition subpart_gbr_2016 values less than (to_date('01-JUL-2017', 'DD-MON-YYYY')) read only,    subpartition subpart_gbr_2017 values less than (to_date('01-JUL-2018', 'DD-MON-YYYY')),    subpartition subpart_gbr_2018 values less than (to_date('01-JUL-2019', 'DD-MON-YYYY'))  ),  partition part_ire values ('IRE') (    subpartition subpart_ire_2016 values less than (to_date('01-JUL-2017', 'DD-MON-YYYY')),    subpartition subpart_ire_2017 values less than (to_date('01-JUL-2018', 'DD-MON-YYYY')) read write,    subpartition subpart_ire_2018 values less than (to_date('01-JUL-2019', 'DD-MON-YYYY')) read write  ));SQL> set linesize 120SQL> column table_name format a20SQL> column partition_name format a20SQL> column subpartition_name format a20SQL> column read_only format a9SQL> select table_name, partition_name,subpartition_name,read_only from user_tab_subpartitions where  table_name = 'T1' order by 1, 2;TABLE_NAME     PARTITION_NAME  SUBPARTITION_NAME    READ_ONLY-------------------- -------------------- -------------------- ---------T1     PART_GBR  SUBPART_GBR_2016     YEST1     PART_GBR  SUBPART_GBR_2017     NOT1     PART_GBR  SUBPART_GBR_2018     NOT1     PART_IRE  SUBPART_IRE_2016     YEST1     PART_IRE  SUBPART_IRE_2017     NOT1     PART_IRE  SUBPART_IRE_2018     NO6 rows selected.

The subpartition status can be switched using the alter table statement.

SQL> alter table t1 modify subpartition subpart_gbr_2016 read write;Table altered.SQL> alter table t1 modify subpartition subpart_gbr_2017 read write;Table altered.SQL> alter table t1 modify subpartition subpart_gbr_2018 read only;Table altered.SQL> alter table t1 modify subpartition subpart_ire_2016 read write;Table altered.SQL> alter table t1 modify subpartition subpart_ire_2017 read write;Table altered.SQL> alter table t1 modify subpartition subpart_ire_2018 read only;Table altered.SQL> select table_name, partition_name,subpartition_name,read_only from user_tab_subpartitions where  table_name = 'T1' order by 1, 2;TABLE_NAME     PARTITION_NAME  SUBPARTITION_NAME    READ_ONLY-------------------- -------------------- -------------------- ---------T1     PART_GBR  SUBPART_GBR_2016     NOT1     PART_GBR  SUBPART_GBR_2017     NOT1     PART_GBR  SUBPART_GBR_2018     YEST1     PART_IRE  SUBPART_IRE_2016     NOT1     PART_IRE  SUBPART_IRE_2017     NOT1     PART_IRE  SUBPART_IRE_2018     YES6 rows selected.

An error occurs when you try to insert a read-only subpartition.

SQL> insert into t1 values (1, 'GBR', 'Description for GBR', to_date('01-JUL-2018', 'DD-MON-YYYY'));insert into t1 values (1, 'GBR', 'Description for GBR', to_date('01-JUL-2018', 'DD-MON-YYYY'))            *ERROR at line 1:ORA-14466: Data in a read-only partition or subpartition cannot be modified.