8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Read-Only Partitions

Related articles.

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:

Hope this helps. Regards Tim...

Back to the Top.