爱站程序员基地在oracle中，我们可以通过如下的命令去split分区，可以保留原分区名。达梦7和达梦8不能保留原分区名，我们用如下的命令来进行测试:
oracle 11g：
CREATE TABLE HC.PARTITION_HB
( PID NUMBER NOT NULL ,
PITEM VARCHAR2(200),
PDATA DATE NOT NULL
)
PARTITION BY RANGE (PID)
(PARTITION PART_01 VALUES LESS THAN (5) ,
PARTITION PART_02 VALUES LESS THAN (10) ,
PARTITION PART_03 VALUES LESS THAN (20) ,
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE) ) ;
BEGIN
FOR I IN 1..150 LOOP
INSERT INTO HC.PARTITION_HB VALUES(I,\’AA\’||I,sysdate);
COMMIT;
END LOOP;
END;
alter table hc.partition_hb SPLIT PARTITION part_max AT(150) INTO
(PARTITION PART_04,PARTITION PART_MAX);
select * from hc.partition_hb partition(part_max);
ALTER TABLE hc.partition_hb RENAME PARTITION part_max TO part_max1;
执行成功，可以用rename来重命名分区
———————————————————————————
DM7 中:
create user hc identified by dameng123;
grant dba to hc;
CREATE TABLE HC.PARTITION_HB
( PID NUMBER NOT NULL ENABLE,
PITEM VARCHAR2(200),
PDATA DATE NOT NULL ENABLE
)
PARTITION BY RANGE (PID)
(PARTITION PART_01 VALUES LESS THAN (5) ,
PARTITION PART_02 VALUES LESS THAN (10) ,
PARTITION PART_03 VALUES LESS THAN (20) ,
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE) ) ;
BEGIN
FOR I IN 1..150 LOOP
INSERT INTO HC.PARTITION_HB VALUES(I,\’AA\’||I,sysdate);
COMMIT;
END LOOP;
END;
select count(1) from hc.partition_hb partition(part_max);
alTer table hc.partition_hb SPLIT PARTITION part_max AT(150) INTO
(PARTITION PART_04,PARTITION PART_MAX1);
ALTER TABLE hc.partition_hb RENAME PARTITION part_max1 TO part_max;
[执行语句1]:
ALTER TABLE hc.partition_hb RENAME PARTITION part_max1 TO part_max;
执行失败(语句1)
第 1 行, 第 74 列[PARTITION]附近出现错误[-2007]:
语法分析出错
可以看到达梦7不支持rename，也不支持对象重名，也就是split后的分区在原始表中不能有，故只能改成part_max1；
——————————————————————————————————————————
dm8：
[root@dm8os tool]# ./disql sysdba/dameng123
disql V8
SQL> show user;
未知的 SHOW 选项 user
SQL> select user;
未连接
SQL> conn sysdba/dameng123
服务器[LOCALHOST:5236]:处于普通配置状态
登录使用时间: 4.958(毫秒)
SQL> create user hc identified by dameng123;
grant dba to hc;
create user hc identified by dameng123;
[-510]:系统处于MOUNT状态.
已用时间: 21.042(毫秒). 执行号:0.
SQL> grant dba to hc;
[-510]:系统处于MOUNT状态.
已用时间: 2.532(毫秒). 执行号:0.
SQL>
SQL> CREATE TABLE HC.PARTITION_HB
2 ( PID NUMBER NOT NULL ENABLE,
3 PITEM VARCHAR2(200),
4 PDATA DATE NOT NULL ENABLE
5 )
6 PARTITION BY RANGE (PID)
7 (PARTITION PART_01 VALUES LESS THAN (5) ,
8 PARTITION PART_02 VALUES LESS THAN (10) ,
9 PARTITION PART_03 VALUES LESS THAN (20) ,
10 PARTITION PART_MAX VALUES LESS THAN (MAXVALUE) ) ;
CREATE TABLE HC.PARTITION_HB
( PID NUMBER NOT NULL ENABLE,
PITEM VARCHAR2(200),
PDATA DATE NOT NULL ENABLE
)
PARTITION BY RANGE (PID)
(PARTITION PART_01 VALUES LESS THAN (5) ,
PARTITION PART_02 VALUES LESS THAN (10) ,
PARTITION PART_03 VALUES LESS THAN (20) ,
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE) ) ;
[-510]:系统处于MOUNT状态.
已用时间: 3.614(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 00:00:01.924. 执行号:0.
SQL> create user hc identified by dameng123;
create user hc identified by dameng123;
第1 行附近出现错误[-2124]:对象[HC]已存在.
已用时间: 1.310(毫秒). 执行号:0.
SQL> grant dba to hc;
操作已执行
已用时间: 11.131(毫秒). 执行号:4.
SQL>
SQL> CREATE TABLE HC.PARTITION_HB
2 ( PID NUMBER NOT NULL ENABLE,
3 PITEM VARCHAR2(200),
4 PDATA DATE NOT NULL ENABLE
5 )
6 PARTITION BY RANGE (PID)
7 (PARTITION PART_01 VALUES LESS THAN (5) ,
8 PARTITION PART_02 VALUES LESS THAN (10) ,
9 PARTITION PART_03 VALUES LESS THAN (20) ,
10 PARTITION PART_MAX VALUES LESS THAN (MAXVALUE) ) ;
操作已执行
已用时间: 28.160(毫秒). 执行号:5.
SQL>
2 BEGIN
3 FOR I IN 1..150 LOOP
4 INSERT INTO HC.PARTITION_HB VALUES(I,\’AA\’||I,sysdate);
5 COMMIT;
6 END LOOP;
7 END;
8 /
DMSQL 过程已成功完成
已用时间: 257.421(毫秒). 执行号:6.
SQL> select count(1) from hc.partition_hb partition(part_max);
行号 COUNT(1)
———- ——————–
1 131
已用时间: 2.695(毫秒). 执行号:7.
SQL> alTer table hc.partition_hb SPLIT PARTITION part_max AT(150) INTO
2 (PARTITION PART_04,PARTITION PART_MAX);
alTer table hc.partition_hb SPLIT PARTITION part_max AT(150) INTO
(PARTITION PART_04,PARTITION PART_MAX);
第2 行附近出现错误[-2622]:分区名与数据库对象名称冲突.
已用时间: 0.757(毫秒). 执行号:0.
SQL> alTer table hc.partition_hb SPLIT PARTITION part_max AT(150) INTO
2 (PARTITION PART_04,PARTITION PART_MAX1);
操作已执行
已用时间: 90.611(毫秒). 执行号:8.
SQL> ALTER TABLE hc.partition_hb RENAME PARTITION part_max1 TO part_max;
操作已执行
已用时间: 117.870(毫秒). 执行号:9.
SQL>
——————————————-
总结: split分区时,oracle可以使用原有分区名,DM中不可以，DM8支持rename partition操作，dm7不支持。
高版本还是功能更完善啊^_^
