12.2的分區新特性中引入了隻讀分區的特性,可以幫助我們将某些分區的資料進行靜态化保護。
這一特性通過将表或者分區設定為read only或者read write來進行控制,以下是一個測試demo(可以在 https://livesql.oracle.com )網站驗證體驗。
以下測試首先将資料表置為read only模式,對部分分區設定為read write的讀寫模式:
drop table yhem_ods; create table yhem_ods (oid number, odate date, omount number ) read only partition by range(odate) (partition q1_2016 values less than (to_date('2016-04-01','yyyy-mm-dd')), partition q2_2016 values less than (to_date('2016-07-01','yyyy-mm-dd')), partition q3_2016 values less than (to_date('2016-10-01','yyyy-mm-dd')) read write, partition q4_2016 values less than (to_date('2017-01-01','yyyy-mm-dd')) read write) / insert into yhem_ods values(1,to_date('2016-01-20','yyyy-mm-dd'),100); insert into yhem_ods values(1,to_date('2016-10-20','yyyy-mm-dd'),100); insert into yhem_ods values(1,to_date('2016-12-20','yyyy-mm-dd'),100); select * from yhem_ods; select * from user_tab_partitions; alter table yhem_ods modify partition q2_2016 read write;
如果向着隻讀分區插入資料,則會收到ora-14466的錯誤提示:
讀寫分區可以自有的進行資料變更:
最後可以查詢這些分區的資料:
分區的屬性可以通過modify語句進行線上修改。
select partition_name, read_only from user_tab_partitions;
通過分區表增加的『read_only』字段可以檢視哪些分區被設定為隻讀狀态:
在官方手冊上有如下的範例,對表、分區和子分區都做出了設定,初始狀态全表設定為讀寫,部分分區設定為隻讀狀态:
create table orders_read_write_only ( order_id number (12), order_date date constraint order_date_nn not null, state varchar2(2) ) read write partition by range (order_date) subpartition by list (state) ( partition order_p1 values less than (to_date ('01-dec-2015','dd-mon-yyyy')) read only ( subpartition order_p1_northwest values ('or', 'wa'), subpartition order_p1_southwest values ('az', 'ut', 'nm') ), partition order_p2 values less than (to_date ('01-mar-2016','dd-mon-yyyy')) ( subpartition order_p2_northwest values ('or', 'wa'), subpartition order_p2_southwest values ('az', 'ut', 'nm') read only), partition order_p3 values less than (to_date ('01-jul-2016','dd-mon-yyyy')) ( subpartition order_p3_northwest values ('or', 'wa') read only, subpartition order_p3_southwest values ('az', 'ut', 'nm') ) ); select partition_name, read_only from user_tab_partitions where table_name ='orders_read_write_only'; select partition_name, subpartition_name, read_only from user_tab_subpartitions where table_name ='orders_read_write_only';
了解了oracle這些新的變化,将有助于我們深入和靈活的去使用oracle資料庫。