**************************
当删除分区表分区的时候,drop 和truncate都会导致global索引失效,需要注意。
可以加参数 update global indexes 在11g的时候,这个参数会影响数据库性能,所以我们一般都是之后手动rebuild索引。
但是在12c及以后,这个参数更加智能,会把工作安排到数据库空闲状态进行,但是我们一般还是选择把删除分区这种操作在空闲时候执行,然后手动重建索引。
对于本地索引,删除分区表的时候不会影响到本地索引的状态。
**************************
当分区表有最大分区的时候,不能直接添加分区,就需要用到split partition:
下面是示例:
create table a (id number, name varchar2(30),name1 varchar2(30),name2 varchar2(30),time date) partition by range (time)
(
partition p2013 values less than (to_date('2014-01-01', 'yyyy-mm-dd')),
partition p2014 values less than (to_date('2015-01-01', 'yyyy-mm-dd')),
partition p2015 values less than (to_date('2016-01-01', 'yyyy-mm-dd')),
partition p2016 values less than (to_date('2017-01-01', 'yyyy-mm-dd')),
partition p2017 values less than (to_date('2018-01-01', 'yyyy-mm-dd')),
partition p2018 values less than (to_date('2019-01-01', 'yyyy-mm-dd')),
partition p2019 values less than (to_date('2020-01-01', 'yyyy-mm-dd')),
partition pmax values less than (maxvalue)
);
select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';
SQL> select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST1 A P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A PMAX MAXVALUE
8 rows selected.
下面我们加分区:
SQL> alter table test1.a add partition "p2020" values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace "TEST1";
alter table test1.a add partition "p2020" values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace "TEST1"
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
alter table test1.a add partition "P2012" values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace "TEST1";
SQL> alter table test1.a add partition "P2012" values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace "TEST1";
alter table test1.a add partition "P2012" values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace "TEST1"
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
因为已经有MAXVALUE这个值,这个时候不允许直接增加分区,而是通过拆分分区实现的。
alter table test1.a add partition "p2020" values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace "TEST1";
增加分区p2020
alter table a split partition pmax at(to_date('2021-01-01','yyyy-mm-dd')) INTO (PARTITION P2020,partition pmax);
SQL> select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST1 A P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2020 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A PMAX MAXVALUE
9 rows selected.
增加分区p2012
alter table a split partition p2013 at (to_date('2013-01-01','yyyy-mm-dd'))INTO (PARTITION p2012,partition p2013);
SQL> select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST1 A P2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A P2020 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST1 A PMAX MAXVALUE
10 rows selected.
自己总结语法:
alter table table_owner.table_name splite partition_name at (partition_values) into (PARTITION partiition_name TABLESPACE tablespace_name NOCOMPRESS,PARTITION partition_new_name TABLESPACE tablespace_name NOCOMPRESS) parallel 16;
解释:
partition_name是已经存在的分区。 partition_new_name 是我们要生成的分区。
可以指定表空间和是否压缩。 NOCOMPRESS
可以并行。