Oracle Database 12c第2版（12.2）中的只读分区和子分区

从Oracle Database 12c第2版（12.2）开始，您可以将分区和子分区标记为只读，以保护其数据免受意外更改。

通过在CREATE TABLE语句的分区级别指定READ ONLY子句，可以使用只读分区创建分区表。

下面用实验来测试一下:

1.只读分区

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

SQL> set linesize 100
SQL> column table_name format a30
SQL> column partition_name format a30
SQL> column read_only format a9
SQL> 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> 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
);

SQL> set linesize 100
SQL> column table_name format a30
SQL> column partition_name format a30
SQL> column read_only format a9
SQL> 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

可以使用ALTER TABLE语句来切换分区的状态。

QL> alter table t1 modify partition t1_2016 read write;

Table altered.

SQL> alter table t1 modify partition t1_2017 read write;

Table altered.

SQL> alter table t1 modify partition t1_2018 read only;

Table altered.

SQL> 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> insert into t1 values (1, 'ONE', 'Description for ONE', sysdate);
insert into t1 values (1, 'ONE', 'Description for ONE', sysdate)
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

2.只读子分区

通过在CREATE TABLE语句的子分区级别指定READ ONLY子句，可以使用只读子分区来创建子分区表

SQL> 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'))
  )
);

SQL> set linesize 120
SQL> column table_name format a20
SQL> column partition_name format a20
SQL> column subpartition_name format a20
SQL> column read_only format a9
SQL> 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

6 rows selected.

或者，可以将表或分区创建为只读，其中一些子分区被标记为可读写。

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

SQL> set linesize 120
SQL> column table_name format a20
SQL> column partition_name format a20
SQL> column subpartition_name format a20
SQL> column read_only format a9
SQL> 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

6 rows selected.

子分区的状态可以使用ALTER TABLE语句进行切换。

SQL> alter table t1 modify subpartition subpart_gbr_2016 read write;

Table altered.

SQL> alter table t1 modify subpartition subpart_gbr_2017 read write;

Table altered.

SQL> alter table t1 modify subpartition subpart_gbr_2018 read only;

Table altered.

SQL> alter table t1 modify subpartition subpart_ire_2016 read write;

Table altered.

SQL> alter table t1 modify subpartition subpart_ire_2017 read write;

Table altered.

SQL> alter table t1 modify subpartition subpart_ire_2018 read only;

Table altered.

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

6 rows selected.

尝试插入只读子分区会导致错误。

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.