DB : 11.2.0.2
OS : AIX 6.1
I am getting the problem while exchanging data with interval partitioned table. I have a interval partitioned table and a normal staging table having data to be uploaded.
Following are the steps i am doing.
SQL> CREATE TABLE DEMO_INTERVAL_DATA_LOAD (
ROLL_NUM NUMBER(10),
CLASS_ID NUMBER(2),
ADMISSION_DATE DATE,
TOTAL_FEE NUMBER(4),
COURSE_ID NUMBER(4))
PARTITION BY RANGE (ADMISSION_DATE)
INTERVAL (NUMTOYMINTERVAL(3,'MONTH'))
( PARTITION QUAT_1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
PARTITION QUAT_2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
PARTITION QUAT_3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
PARTITION QUAT_4_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')));
Table created.
SQL> ALTER TABLE DEMO_INTERVAL_DATA_LOAD ADD CONSTRAINT IDX_DEMO_ROLL PRIMARY KEY (ROLL_NUM);
Table altered.
SQL> SELECT TABLE_OWNER,
TABLE_NAME,
COMPOSITE,
PARTITION_NAME,
PARTITION_POSITION,
TABLESPACE_NAME,
LAST_ANALYZED
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER='SCOTT'
AND TABLE_NAME='DEMO_INTERVAL_DATA_LOAD'
ORDER BY PARTITION_POSITION;
TABLE_OWNER TABLE_NAME COM PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME LAST_ANAL
------------------------------ ------------------------------ --- ------------------------------ ------------------ ------------------------------ ---------
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_1_2012 1 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_2_2012 2 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_3_2012 3 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_4_2012 4 USERS
SQL> INSERT INTO DEMO_INTERVAL_DATA_LOAD VALUES (10,1,'12-MAR-2012',1000,90);
1 row created.
SQL> INSERT INTO DEMO_INTERVAL_DATA_LOAD VALUES (11,5,'01-JUN-2012',5000,80);
1 row created.
SQL> INSERT INTO DEMO_INTERVAL_DATA_LOAD VALUES (12,9,'12-SEP-2012',4000,20);
1 row created.
SQL> INSERT INTO DEMO_INTERVAL_DATA_LOAD VALUES (13,7,'29-DEC-2012',7000,10);
1 row created.
SQL> INSERT INTO DEMO_INTERVAL_DATA_LOAD VALUES (14,8,'21-JAN-2013',2000,50); ---- This row will create a new interval partition in table.
1 row created.
SQL> commit;
SQL> SELECT TABLE_OWNER,
TABLE_NAME,
COMPOSITE,
PARTITION_NAME,
PARTITION_POSITION,
TABLESPACE_NAME,
LAST_ANALYZED
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER='SCOTT'
AND TABLE_NAME='DEMO_INTERVAL_DATA_LOAD'
ORDER BY PARTITION_POSITION;
TABLE_OWNER TABLE_NAME COM PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME LAST_ANAL
------------------------------ ------------------------------ --- ------------------------------ ------------------ ------------------------------ ---------
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_1_2012 1 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_2_2012 2 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_3_2012 3 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_4_2012 4 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO SYS_P98 5 USERS
SYS_P98 partition is added to table automatically.
SQL> CREATE TABLE DEMO_INTERVAL_DATA_LOAD_Y (
ROLL_NUM NUMBER(10),
CLASS_ID NUMBER(2),
ADMISSION_DATE DATE,
TOTAL_FEE NUMBER(4),
COURSE_ID NUMBER(4));
Table created.
SQL> INSERT INTO DEMO_INTERVAL_DATA_LOAD_Y VALUES (30,3,'21-MAY-2013',2000,12);
1 row created.
SQL> commit;
Commit complete.
Since, i need a partition in DEMO_INTERVAL_DATA_LOAD table, which can be used in partition exchange, so i create a new partition as below:
SQL> LOCK TABLE DEMO_INTERVAL_DATA_LOAD PARTITION FOR (TO_DATE('01-APR-2013','DD-MON-YYYY')) IN SHARE MODE;
Table(s) Locked.
SQL> SELECT TABLE_OWNER,
TABLE_NAME,
COMPOSITE,
PARTITION_NAME,
PARTITION_POSITION,
TABLESPACE_NAME,
LAST_ANALYZED
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER='SCOTT'
AND TABLE_NAME='DEMO_INTERVAL_DATA_LOAD'
ORDER BY PARTITION_POSITION;
TABLE_OWNER TABLE_NAME COM PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME LAST_ANAL
------------------------------ ------------------------------ --- ------------------------------ ------------------ ------------------------------ ---------
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_1_2012 1 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_2_2012 2 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_3_2012 3 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO QUAT_4_2012 4 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO SYS_P98 5 USERS
SCOTT DEMO_INTERVAL_DATA_LOAD NO SYS_P102 6 USERS
SQL> ALTER TABLE DEMO_INTERVAL_DATA_LOAD
EXCHANGE PARTITION SYS_P102
WITH TABLE DEMO_INTERVAL_DATA_LOAD_Y
INCLUDING INDEXES
WITH VALIDATION;
ALTER TABLE DEMO_INTERVAL_DATA_LOAD
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Now, if i disable/drop the primary key constraint, it works without any problem.
SQL> alter table DEMO_INTERVAL_DATA_LOAD disable constraint IDX_DEMO_ROLL;
Table altered.
SQL> alter table DEMO_INTERVAL_DATA_LOAD drop constraint IDX_DEMO_ROLL;
Table altered.
SQL> ALTER TABLE DEMO_INTERVAL_DATA_LOAD
EXCHANGE PARTITION SYS_P102
WITH TABLE DEMO_INTERVAL_DATA_LOAD_Y
INCLUDING INDEXES
WITH VALIDATION;
Table altered.
SQL> select * from DEMO_INTERVAL_DATA_LOAD partition (SYS_P102);
ROLL_NUM CLASS_ID ADMISSION TOTAL_FEE COURSE_ID
---------- ---------- --------- ---------- ----------
30 3 21-MAY-13 2000 12
SQL> select * from DEMO_INTERVAL_DATA_LOAD_Y;
no rows selected
Please suggest.