Oracle分区表基础运维-07增加分区(范围分区)

IT小Chen 2021-04-13

1875

About Adding Partitions and Subpartitions

• Adding a Partition to a Range-Partitioned Table

• Adding a Partition to a Hash-Partitioned Table

• Adding a Partition to a List-Partitioned Table

• About Adding Partitions to a Composite *-Hash Partitioned Table

• About Adding Partitions to a Composite *-List Partitioned Table

• About Adding Partitions to a Composite *-Range Partitioned Table

1 范围分区表增加分区

Adding a Partition to a Range-Partitioned Table

---查看当前分区表

select table_name,
       partitioning_type,
       subpartitioning_type,
       partition_count,
       status,
       def_tablespace_name
  from user_part_tables
 order by 1;

---查看范围分区表建表语句

select dbms_metadata.get_ddl('TABLE','RANGE_PART_TAB','CJC') from dual;

......

SQL> SELECT table_name,partition_name,high_value from user_tab_partitions where table_name='RANGE_PART_TAB';

insert into RANGE_PART_TAB values(1,sysdate+30,592,'aaa');
insert into RANGE_PART_TAB values(1,sysdate+31,592,'aaa');
insert into RANGE_PART_TAB values(1,sysdate+32,592,'aaa');
commit;
select * from RANGE_PART_TAB order by 2 desc;

select * from RANGE_PART_TAB partition(P_MAX) order by 2 desc;

向范围分区表添加分区，报错ORA-14074

ALTER TABLE RANGE_PART_TAB ADD PARTITION P13 VALUES LESS THAN (TO_DATE('2020-06-01','YYYY-MM-DD'));

ALTER TABLE RANGE_PART_TAB ADD PARTITION P13 VALUES LESS THAN (TO_DATE('2020-07-01','YYYY-MM-DD'));

因为maxvalue分区的存在,不允许直接增加分区。可以通过拆分分区实现，如下：

将P_MAX分区内，分区键2020年5月份的数据拆分到新分区P13内。

ALTER TABLE RANGE_PART_TAB split PARTITION P_MAX at (TO_DATE('2020-06-01','YYYY-MM-DD')) into (partition P13,partition P_MAX);

拆分完成，查看新分区P13的数据

select * from RANGE_PART_TAB partition(P13) order by 2 desc;

查看P_MAX分区已经没有5月份的数据了

select * from RANGE_PART_TAB partition(P_MAX) order by 2 desc;

删除P_MAX分区，对应6月份的数据全部删除

ALTER TABLE RANGE_PART_TAB drop partition P_MAX;

没有p_max分区，不允许插入6月份的数据

insert into RANGE_PART_TAB values(1,sysdate+30,592,'aaa');

此时没有P_MAX分区了，可以直接添加新分区P14

ALTER TABLE RANGE_PART_TAB ADD PARTITION p14 VALUES LESS THAN (TO_DATE('2020-07-01','YYYY-MM-DD')) tablespace cjctbs;

插入信息数据

insert into RANGE_PART_TAB values(1,sysdate+30,592,'aaa');
insert into RANGE_PART_TAB values(1,sysdate+31,592,'aaa');
insert into RANGE_PART_TAB values(1,sysdate+32,592,'aaa');
commit;

查看P14分区数据

select * from RANGE_PART_TAB partition(P14) order by 2 desc;

Local and global indexes associated with the range-partitioned table remain usable.

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle分区表基础运维-07增加分区(范围分区)

评论