Team,

Started reading about Read Only partitions in 12.2

http://docs.oracle.com/database/122/VLDBG/partition-create-tables-indexes.htm#VLDBG-GUID-9D7149B6-A2FF-47CA-8F00-47CBFD33F82B

<quote>
A higher level setting of the read-only clause is applied to partitions and subpartitions unless the read-only clause has been explicitly set for a partition or subpartition.
</quote>

demo@ORA12C> create table t1
  2  partition by list(x)
  3  subpartition by list(y)
  4  subpartition template(
  5     subpartition sp1 values (0) READ ONLY ,
  6     subpartition sp2 values (1) )
  7  (  partition p1 values (0) READ ONLY ,
  8     partition p2 values (1) READ WRITE ,
  9     partition p3 values (2) )
 10  as
 11  select mod(rownum,3) as x, mod(rownum,2) as y ,
 12             object_name, created
 13  from all_objects
 14  where rownum <= 100;

Table created.

demo@ORA12C> column table_name format a10
demo@ORA12C> column partition_name format a10
demo@ORA12C> column subpartition_name format a10
demo@ORA12C> select table_name,partition_name,subpartition_name,read_only
  2  from user_tab_subpartitions
  3  where table_name ='T1'
  4  order by partition_name,subpartition_name;

TABLE_NAME PARTITION_ SUBPARTITI REA
---------- ---------- ---------- ---
T1         P1         P1_SP1     YES
T1         P1         P1_SP2     YES
T1         P2         P2_SP1     NO  <======( This confuses me )
T1         P2         P2_SP2     NO
T1         P3         P3_SP1     YES
T1         P3         P3_SP2     NO

6 rows selected.

demo@ORA12C>

Subpartition SP1 is set to READ ONLY explicitly at the subpartition template.
Partition P2 is set to READ WRITE explicitly at partition level.

Since the subpartition SP1 is set to READ ONLY explicitly, across all the partitions this Sub-partition should be read only, but how ever that is not the case here( please see the partition P2 above).

without sub-partition template, things went like this.

demo@ORA12C> drop table t1 purge;

Table dropped.

demo@ORA12C> create table t1
  2  partition by list(x)
  3  subpartition by list(y)
  4  (  partition p1 values (0) READ ONLY
  5             (       subpartition p1_sp1 values (0) READ ONLY ,
  6                     subpartition p1_sp2 values (1) ) ,
  7      partition p2 values (1) READ WRITE
  8             (   subpartition p2_sp1 values (0) READ ONLY ,
  9                     subpartition p2_sp2 values (1) ) ,
 10     partition p3 values (2)
 11             (       subpartition p3_sp1 values (0) READ ONLY ,
 12                     subpartition p3_sp2 values (1) )
 13     )
 14  as
 15  select mod(rownum,3) as x, mod(rownum,2) as y ,
 16             object_name, created
 17  from all_objects
 18  where rownum <= 100;

Table created.

demo@ORA12C> column table_name format a10
demo@ORA12C> column partition_name format a10
demo@ORA12C> column subpartition_name format a10
demo@ORA12C> select table_name,partition_name,subpartition_name,read_only
  2  from user_tab_subpartitions
  3  where table_name ='T1'
  4  order by partition_name,subpartition_name;

TABLE_NAME PARTITION_ SUBPARTITI REA
---------- ---------- ---------- ---
T1         P1         P1_SP1     YES
T1         P1         P1_SP2     YES
T1         P2         P2_SP1     YES <====( this looks perfect to me)
T1         P2         P2_SP2     NO
T1         P3         P3_SP1     YES
T1         P3         P3_SP2     NO

6 rows selected.

demo@ORA12C>

are we hitting any bug here? please correct me on this understanding about READ ONLY partition in the context of Composite partitioned tables.