You Asked
Hi,
I want to do partition exchange from Table A to Table B.
Following is one test case,
create table RANGE_HASH_TEST
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26)
)
PARTITION BY RANGE (TRN_DT)
SUBPARTITION BY HASH (REF_NO) SUBPARTITIONS 4
( PARTITION Acc_Jan_2017 VALUES LESS THAN (TO_DATE('01-Feb-2017','dd-MON-yyyy')));
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),1,'123');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),2,'12');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),3,'113');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),4,'121');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),5,'124');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),6,'125');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),7,'126');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),8,'127');
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),9,'128');
COMMIT;
create table RANGE_HASH_TEST_HIST
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26)
)
PARTITION BY RANGE (TRN_DT)
SUBPARTITION BY HASH (REF_NO) SUBPARTITIONS 8
( PARTITION Acc_Jan_2017 VALUES LESS THAN (TO_DATE('01-Feb-2017','dd-MON-yyyy')));
ALTER TABLE RANGE_HASH_TEST EXCHANGE PARTITION ACC_JAN_2017 WITH TABLE RANGE_HASH_TEST_HIST;
ERROR at line 1:
ORA-14292: Partitioning type of table must match subpartitioning type of composite partition
Please guide me on below doubts,
1) Is it possible to move Range-Hash partition to another partition table as described in above test case? (from one partition table to another partition table)
2) If no, any alternate way to do the same?
3) If yes, How to achieve the same?
Thank you for your kind guidance.
Chandan
and Connor said...
They dont match because you are trying to swap an partition with 4 subpartitions with into one that has 8 subpartitions. We would have to move all the data to do that.
But if the tables have the same subpartition structure, you can do an exchange in two steps
SQL> create table RANGE_HASH_TEST
2 (
3 TRN_DT DATE,
4 SEQ_NO NUMBER,
5 REF_NO VARCHAR2(26)
6 )
7 PARTITION BY RANGE (TRN_DT)
8 SUBPARTITION BY HASH (REF_NO) SUBPARTITIONS 4
9 ( PARTITION Acc_Jan_2017 VALUES LESS THAN (TO_DATE('01-Feb-2017','dd-MON-yyyy')));
Table created.
SQL>
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),1,'123');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),2,'12');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),3,'113');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),4,'121');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),5,'124');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),6,'125');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),7,'126');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),8,'127');
1 row created.
SQL> INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),9,'128');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> create table range_exch
2 (
3 TRN_DT DATE,
4 SEQ_NO NUMBER,
5 REF_NO VARCHAR2(26)
6 )
7 PARTITION BY HASH (REF_NO) PARTITIONS 4;
Table created.
SQL>
SQL> ALTER TABLE RANGE_HASH_TEST EXCHANGE PARTITION ACC_JAN_2017 WITH TABLE range_exch;
Table altered.
SQL>
SQL> create table RANGE_HASH_TEST_HIST
2 (
3 TRN_DT DATE,
4 SEQ_NO NUMBER,
5 REF_NO VARCHAR2(26)
6 )
7 PARTITION BY RANGE (TRN_DT)
8 SUBPARTITION BY HASH (REF_NO) SUBPARTITIONS 4
9 ( PARTITION Acc_Jan_2017 VALUES LESS THAN (TO_DATE('01-Feb-2017','dd-MON-yyyy')));
Table created.
SQL> ALTER TABLE RANGE_HASH_TEST_HIST EXCHANGE PARTITION ACC_JAN_2017 WITH TABLE range_exch;
Table altered.
SQL>
Rating
(7 ratings)
Is this answer out of date? If it is, please let us know via a Comment