OracleDatabase12c第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.
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。