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

IT小Chen 2021-04-13

1451

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

6 RANGE_RANGE组合分区表增加分区

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

创建range-range分区表

---RANGE-HASH

CREATE TABLE range_range_part_tab
( order_id NUMBER NOT NULL
, order_date DATE NOT NULL
, delivery_date DATE NOT NULL
, customer_id NUMBER NOT NULL
, sales_amount NUMBER NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 , SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 ( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))
 , SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
 , SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
 )
);

查看分区信息

SELECT * from user_tab_partitions where table_name='RANGE_RANGE_PART_TAB';

SELECT * from user_tab_subpartitions where table_name='RANGE_RANGE_PART_TAB'order by 2,3;

增加新分区P_2007_JAN，指定三个子分区

• Adding a Partition to a *-Range Partitioned Table

ALTER TABLE RANGE_RANGE_PART_TAB
 ADD PARTITION p_2007_jan
 VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')) COMPRESS
 ( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))
 , SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
 , SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))
 ) ;

查看分区信息

SELECT * from user_tab_partitions where table_name='RANGE_RANGE_PART_TAB';

查看子分区信息

SELECT * from user_tab_subpartitions where table_name='RANGE_RANGE_PART_TAB'and partition_name='P_2007_JAN';

增加子分区

• Adding a Subpartition to a *-Range Partitioned Table

ALTER TABLE RANGE_RANGE_PART_TAB
 MODIFY PARTITION p_2007_jan
 ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE) ;

查看子分区信息

SELECT * from user_tab_subpartitions where table_name='RANGE_RANGE_PART_TAB'and partition_name='P_2007_JAN';

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

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

oracle

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

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

评论