Read Only Table Partitions

Read-Only Partitions

Starting with Oracle version 12.2, partitions or sub-partitions of a table can be marked as read-only. This can also be specified during table creation.

This can be specified with the phrase “READ ONLY”. The default behavior is “READ WRITE”.

The Advantages of Read-Only Partitions :

Read-only partitions are particularly useful when dealing with historical data, archival information, compliance records, or datasets designated for reporting and auditing purposes. They ensure data consistency and accuracy while still being included in database backups. As read-only partitions do not generate redo logs, RMAN’s block-change-tracking feature ignores them during incremental backups. This results in faster backup operations and reduced storage requirements. Although it is possible to switch a partition from read-only to read-write mode, doing so requires specific database privileges and is a deliberate administrative action.

To make existing and future partitions of a partitioned table read-only:

ALTER TABLE my_table READ ONLY;

To make only the part01 section of a partitioned table read-only:

ALTER TABLE my_table MODIFY PARTITION part01 READ ONLY;

To undo the change, you can replace “READ ONLY” with “READ WRITE”.

When creating a table, the partition we want to be read-only can be specified using the “READ ONLY” clause in the `create table` statement.

It is possible to create read-only subpartitions as shown below.

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) )
   )

The following query can be used to determine whether the 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;

The following query can be used to determine 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