CREATE TABLE dmhr.emp_part
(
EMPLOYEE_ID INT,
EMPLOYEE_NAME VARCHAR (20),
IDENTITY_CARD VARCHAR (18),
EMAIL VARCHAR (50) NOT NULL,
PHONE_NUM VARCHAR (20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR (10) NOT NULL,
SALARY INT,
COMMISSION_PCT INT,
MANAGER_ID INT,
DEPARTMENT_ID INT
)
PARTITION BY RANGE (hire_date) INTERVAL ( NUMTOYMINTERVAL (1, 'year') )
(
PARTITION P_BEFORE_2007 VALUES LESS THAN (TO_DATE ('2007-01-01', 'yyyy-mm-dd'))
)
STORAGE (FILLFACTOR 85, BRANCH(32,32));
INSERT INTO dmhr.emp_part SELECT * FROM dmhr.employee;
查看分区信息
SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE from DBA_TAB_PARTITIONS where TABLE_NAME = 'emp_part' ORDER BY HIGH_VALUE;
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- ----------------
EMP_PART P_BEFORE_2007 DATE'2007-01-01'
EMP_PART SYS_P1291_1293 DATE'2009-01-01'
EMP_PART SYS_P1291_1307 DATE'2010-01-01'
EMP_PART SYS_P1291_1297 DATE'2011-01-01'
EMP_PART SYS_P1291_1299 DATE'2012-01-01'
EMP_PART SYS_P1291_1295 DATE'2013-01-01'
EMP_PART SYS_P1291_1303 DATE'2014-01-01'
EMP_PART SYS_P1291_1301 DATE'2015-01-01'
EMP_PART SYS_P1291_1305 DATE'2016-01-01'
我想把分区SYS_P1291_1307,交换到表emp_part_2009
CREATE TABLE DMHR.EMP_PART_2009
(
EMPLOYEE_ID INT,
EMPLOYEE_NAME VARCHAR (20),
IDENTITY_CARD VARCHAR (18),
EMAIL VARCHAR (50) NOT NULL,
PHONE_NUM VARCHAR (20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR (10) NOT NULL,
SALARY INT,
COMMISSION_PCT INT,
MANAGER_ID INT,
DEPARTMENT_ID INT
)
交换分区
ALTER TABLE DMHR.EMP_PART EXCHANGE PARTITION SYS_P1291_1307 WITH TABLE "DMHR"."EMP_PART_2009"
执行失败(语句1)
-7000: 第1 行附近出现错误:
交换对象不匹配
我没有额外新建索引,约束什么的
问题:
1、交换分区只能是list跟range分区才能使用?上面间隔分区表,也算属于range分区范围吧?