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

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

阅读量：4070 次

发布时间：2019-05-25

本文共 7481 字，大约阅读时间需要 24 分钟。

从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 100SQL> column table_name format a30SQL> column partition_name format a30SQL> column read_only format a9SQL> 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			      NOT1			       T1_2017			      NOT1			       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 onlypartition 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 100SQL> column table_name format a30SQL> column partition_name format a30SQL> column read_only format a9SQL> 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			      YEST1			       T1_2017			      YEST1			       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			      NOT1			       T1_2017			      NOT1			       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 120SQL> column table_name format a20SQL> column partition_name format a20SQL> column subpartition_name format a20SQL> column read_only format a9SQL> 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     YEST1		     PART_GBR		  SUBPART_GBR_2017     NOT1		     PART_GBR		  SUBPART_GBR_2018     NOT1		     PART_IRE		  SUBPART_IRE_2016     YEST1		     PART_IRE		  SUBPART_IRE_2017     NOT1		     PART_IRE		  SUBPART_IRE_2018     NO6 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 onlypartition 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 120SQL> column table_name format a20SQL> column partition_name format a20SQL> column subpartition_name format a20SQL> column read_only format a9SQL> 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     YEST1		     PART_GBR		  SUBPART_GBR_2017     NOT1		     PART_GBR		  SUBPART_GBR_2018     NOT1		     PART_IRE		  SUBPART_IRE_2016     YEST1		     PART_IRE		  SUBPART_IRE_2017     NOT1		     PART_IRE		  SUBPART_IRE_2018     NO6 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     NOT1		     PART_GBR		  SUBPART_GBR_2017     NOT1		     PART_GBR		  SUBPART_GBR_2018     YEST1		     PART_IRE		  SUBPART_IRE_2016     NOT1		     PART_IRE		  SUBPART_IRE_2017     NOT1		     PART_IRE		  SUBPART_IRE_2018     YES6 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.