Read Only Table Partitions

As of Oracle version 12.2, partitions or sub-partitions of a table can be marked as read-only.?It can also be specified during the table creation phase.

It can be specified with the phrase “READ ONLY”.?Its default behavior is “READ WRITE”.

To make current and future partitions in a partitioned table read-only:

SQL>ALTER TABLE my_table READ ONLY;
        

In order for only the partition named part01 in a partitioned table to be read-only:?

SQL>ALTER TABLE my_table MODIFY PARTITION part01 READ ONLY;
        

Commands can be used.

To undo the change made, "READ WRITE" can be written instead of "READ ONLY".

While creating the table, “READ ONLY” statement can be used in the partition that we want to be a read-only partition in the create table statement.

It is possible to create read only sub partitions as follows.

create table my_table
partition by list(x)
subpartition by list(y)
(  partition p1 values (0) READ ONLY
           (       subpartition p1_sp1 values (0) READ ONLY ,
                   subpartition p1_sp2 values (1) ) ,
    partition p2 values (1) READ WRITE
           (   subpartition p2_sp1 values (0) READ ONLY ,
                   subpartition p2_sp2 values (1) ) ,
   partition p3 values (2)
           (       subpartition p3_sp1 values (0) READ ONLY ,
                   subpartition p3_sp2 values (1) )
   )
        

With the following query, we can learn whether sub partitions in a table are read only or read write.

select table_name,partition_name,subpartition_name,read_only
from dba_tab_subpartitions
where table_name ='MY_TABLE'
order by partition_name,subpartition_name;
        

With the following query, we can learn whether partitions in a table are read only or read write.?

?select table_name,partition_name,read_only
from dba_tab_partitions
where table_name ='MY_TABLE'
order by table_name,partition_name