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

╰半夏微凉° 2022-05-31 05:40 135阅读 0赞

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

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

# ![20180208165309831][]    #

下面用实验来测试一下:

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.

[20180208165309831]: /images/20220531/7e0f3a5e06c543778ecb9bbb57cb3e89.png