oracle拆分分区语法详解大全_Oracle 11g 分区拆分与合并

时间范围分区拆分

create table emp (

id number(6) not null,

hire_date date not null)

partition by range(hire_date)

(partition p_1998 values less than (to_date('1998-12-31','YYYY-MM-DD')),

partition p_1999 values less than (to_date('1999-12-31','YYYY-MM-DD')),

partition p_default values less than (maxvalue)

);

insert into emp values(10,to_date('1999-5-20','YYYY-MM-DD'));

insert into emp values(20,to_date('1999-8-10','YYYY-MM-DD'));

SQL> select count(*) from emp partition(p_1999);

COUNT(*)

----------

2

将p_1999分区拆分成两个分区

SQL> ALTER TABLE emp SPLIT PARTITION p_1999

2     AT (to_date('1999-07-01','YYYY-MM-DD'))

3     INTO (PARTITION p_1999_01, PARTITION p_1999_02);

表已更改。

SQL> select count(*) from emp partition(p_1999_01);

COUNT(*)

----------

1

SQL> select count(*) from emp partition(p_1999_02);

COUNT(*)

----------

1

将p_default分区拆分成两个分区

SQL> insert into emp values(30,to_date('2000-5-27','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into emp values(40,to_date('2001-10-02','YYYY-MM-DD'));

已创建 1 行。

SQL> select count(*) from emp partition(p_default);

COUNT(*)

----------

2

SQL> ALTER TABLE emp SPLIT PARTITION p_default

2         AT (to_date('2000-12-31','YYYY-MM-DD'))

3         INTO (PARTITION p_2000, PARTITION p_default);

表已更改。

SQL> select count(*) from emp partition(p_default);

COUNT(*)

----------

1

SQL> select count(*) from emp partition(p_2000);

COUNT(*)

----------

1

SQL> select * from emp partition(p_default);

ID HIRE_DATE

---------- -------------------

40 2001-10-02 00:00:00

SQL> select * from emp partition(p_2000);

ID HIRE_DATE

---------- -------------------

30 2000-05-27 00:00:00

LIST分区拆分

SQL> create table dept (

2  id number(6) not null,

3  area varchar2(15) not null)

4  partition by list(area)

5  (

6     PARTITION asia VALUES ('CHINA', 'THAILAND'),

7     PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),

8     PARTITION west VALUES ('AMERICA'),

9     PARTITION east VALUES ('INDIA'),

10     PARTITION rest VALUES (DEFAULT));

表已创建。

SQL> insert into dept values(10,'MEXICO');

已创建 1 行。

SQL> insert into dept values(20,'COLOMBIA');

已创建 1 行。

SQL> select count(*) from dept partition(rest);

COUNT(*)

----------

2

SQL> ALTER TABLE dept SPLIT PARTITION rest

2     VALUES ('MEXICO', 'COLOMBIA')

3     INTO (PARTITION south, PARTITION rest);

表已更改。

SQL> select count(*) from dept partition(rest);

COUNT(*)

----------

0

SQL> select count(*) from dept partition(south);

COUNT(*)

----------

2

分区合并

SQL> SELECT table_name,partition_name  FROM USER_tab_partitions where table_name='EMP';

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

EMP                            P_1998

EMP                            P_1999_01

EMP                            P_1999_02

EMP                            P_2000

EMP                            P_DEFAULT

SQL> ALTER TABLE emp

2         MERGE PARTITIONS P_1999_01, P_1999_02 INTO PARTITION P_1999;

表已更改。

SQL> SELECT table_name,partition_name  FROM USER_tab_partitions where table_name='EMP';

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

EMP                            P_1998

EMP                            P_1999

EMP                            P_2000

EMP                            P_DEFAULT

SQL> SELECT table_name,partition_name  FROM USER_tab_partitions where table_name='DEPT';

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

DEPT                           ASIA

DEPT                           EUROPE

DEPT                           WEST

DEPT                           EAST

DEPT                           SOUTH

DEPT                           REST

已选择6行。

SQL> ALTER TABLE dept

2     MERGE PARTITIONS south, rest INTO PARTITION rest;

表已更改。

SQL> select count(*) from dept partition(rest);

COUNT(*)

----------

2