8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Read-Only Partitions and Subpartitions in Oracle Database 12c Release 2 (12.2)
From Oracle Database 12c Release 2 (12.2) onward you can mark partitions and subpartitions as read-only to protect their data from accidental change.
Related articles.
- Read-Only Partitions and Subpartitions in Oracle 12.2 Onward
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
Read-Only Partitions
A partitioned table can be created with read-only partitions by specifying the READ ONLY
clause at the partition level in the CREATE TABLE
statement.
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 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 ); set linesize 100 column table_name format a30 column partition_name format a30 column read_only format a9 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 NO T1 T1_2017 NO T1 T1_2018 YES SQL>
Alternatively the table can be created as read-only, with some partitions marked as read-write by exception.
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 only 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 write ); set linesize 100 column table_name format a30 column partition_name format a30 column read_only format a9 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 YES T1 T1_2017 YES T1 T1_2018 NO SQL>
The status of a partition can be switched using the ALTER TABLE statement.
alter table t1 modify partition t1_2016 read write; alter table t1 modify partition t1_2017 read write; alter table t1 modify partition t1_2018 read only; set linesize 100 column table_name format a30 column partition_name format a30 column read_only format a9 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 NO T1 T1_2017 NO T1 T1_2018 YES SQL>
Attempting to insert into a read-only partition results in an error.
SQL> insert into t1 values (1, 'ONE', 'Description for ONE', to_date('01-jul-2018', 'dd-mon-yyyy')); insert into t1 values (1, 'ONE', 'Description for ONE', 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. SQL>
Read-Only Subpartitions
A subpartitioned table can be created with read-only subpartitions by specifying the READ ONLY
clause at the subpartition level in the CREATE TABLE
statement.
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')) ) ); set linesize 120 column table_name format a20 column partition_name format a20 column subpartition_name format a20 column read_only format a9 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 YES T1 PART_GBR SUBPART_GBR_2017 NO T1 PART_GBR SUBPART_GBR_2018 NO T1 PART_IRE SUBPART_IRE_2016 YES T1 PART_IRE SUBPART_IRE_2017 NO T1 PART_IRE SUBPART_IRE_2018 NO SQL>
Alternatively the table or partition can be created as read-only, with some subpartitions marked as read-write by exception.
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 only partition 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 ) ); set linesize 120 column table_name format a20 column partition_name format a20 column subpartition_name format a20 column read_only format a9 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 YES T1 PART_GBR SUBPART_GBR_2017 NO T1 PART_GBR SUBPART_GBR_2018 NO T1 PART_IRE SUBPART_IRE_2016 YES T1 PART_IRE SUBPART_IRE_2017 NO T1 PART_IRE SUBPART_IRE_2018 NO SQL>
The status of a subpartition can be switched using the ALTER TABLE statement.
alter table t1 modify subpartition subpart_gbr_2016 read write; alter table t1 modify subpartition subpart_gbr_2017 read write; alter table t1 modify subpartition subpart_gbr_2018 read only; alter table t1 modify subpartition subpart_ire_2016 read write; alter table t1 modify subpartition subpart_ire_2017 read write; alter table t1 modify subpartition subpart_ire_2018 read only; set linesize 120 column table_name format a20 column partition_name format a20 column subpartition_name format a20 column read_only format a9 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 NO T1 PART_GBR SUBPART_GBR_2017 NO T1 PART_GBR SUBPART_GBR_2018 YES T1 PART_IRE SUBPART_IRE_2016 NO T1 PART_IRE SUBPART_IRE_2017 NO T1 PART_IRE SUBPART_IRE_2018 YES SQL>
Attempting to insert into a read-only subpartition results in an error.
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. SQL>
For more information see:
- Creating a Table with Read-Only Partitions or Subpartitions
- Read-Only Partitions and Subpartitions in Oracle 12.2 Onward
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...