createtable orders(order_id number,order_date date) readwrite PARTITION BY RANGE(order_date) ( partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')) READONLY, partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) READONLY, partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')), partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd')) );
# 查看user_tab_partitions视图 set linesize 300 col table_namefor a30 col partition_name for a30 col read_only for a30 selecttable_name,partition_name,read_only from user_tab_partitions wheretable_name='ORDERS';
TABLE_NAME PARTITION_NAME READ_ONLY ------------------------------ ------------------------------ ------------------------------ ORDERS Q1_2015 YES ORDERS Q2_2015 YES ORDERS Q3_2015 NO ORDERS Q4_2015 NO
xb@PDB12C> insertinto orders values(1,'2014-09-01'); insertinto orders values(1,'2014-09-01') * ERROR at line1: ORA-14466: Data in a read-onlypartitionor subpartition cannot be modified.
createtable orders(order_id number,order_date date) readonly PARTITION BY RANGE(order_date) ( partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')) READwrite, partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) , partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')), partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd')) );
set linesize 300 col table_namefor a30 col partition_name for a30 col read_only for a30 selecttable_name,partition_name,read_only from user_tab_partitions wheretable_name='ORDERS';
createtable orders(order_id number,order_date date) readwrite PARTITION BY list(order_id) subpartition by range(order_date)( partition q1 values(1) readonly ( subpartition q1_2014 values less than (to_date('2014-10-01','yyyy-mm-dd')), subpartition q1_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) ), partition q2 values(2) ( subpartition q2_2014 values less than (to_date('2014-10-01','yyyy-mm-dd')) readonly, subpartition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) ) );
xb@PDB12C> select partition_name, subpartition_name, read_only from user_tab_subpartitions wheretable_name ='ORDERS';
PARTITION_NAME SUBPARTITION_NAME READ_ONLY ------------------------------ ------------------------------ ------------------------------ Q1 Q1_2014 YES Q1 Q1_2015 NO Q2 Q2_2014 YES Q2 Q2_2015 NO
而状态为只读的子分区也是无法修改数据的
1 2 3 4 5 6 7 8 9 10 11 12 13 14
xb@PDB12C> insertinto orders values(1,'2014-06-01'); insertinto orders values(1,'2014-06-01') * ERROR at line1: ORA-14466: Data in a read-onlypartitionor subpartition cannot be modified.