注册

间隔分区表不能交换分区么

小灰灰_oO 2024/05/06 529 5 已解决
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分区范围吧?

回答 0
暂无回答
扫一扫
联系客服