Thanks for the question, Chandan.

Asked: March 23, 2017 - 7:39 am UTC

Last updated: January 28, 2021 - 10:11 am UTC

Version: 12.1

Viewed 10K+ times! This question is

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

Comments

Chandan Tanwani, March 24, 2017 - 8:31 am UTC

Thank you Connor for your quick response.

It was my typo mistake of SUBPARTITIONS 8 during typing to you, in actual I have created 4 sub partitions only.

Your idea has worked for range-hash partition to exchange range-hash with temp table.

Regards,
Chandan

March 25, 2017 - 3:25 am UTC

glad we could help

different scenario

Peter, April 11, 2017 - 9:23 am UTC

Hello,
as far i understood the question, it was about exchanging partitions between 2 tables, both partitioned by
PARTITION BY RANGE
SUBPARTITION BY HASH
. the answer do not match 100% in my opinion because the tables range_exch is not partitioned by range and subpartitioned by HASH but only partitioned by HASH. This is another scenario i think.

We would be interested in exchange partitions between 2 tables, both partitioned same way (and same partition counts)

PARTITION BY RANGE
SUBPARTITION BY HASH

but we receive only ORA-14292

Thanks in advance

April 12, 2017 - 5:46 am UTC

I think you're misinterpreting. The requirement was do an exchange between:

table1: part by range/subpart by hash

with another table, also:

table2: part by range/subpart by hash

You *cannot* do that with a single command, so we created a hash part table (which hence has the same structure as *one* of the partitions in our original tables). Then we do:

a) exchange table1 part1 with standalone table (hash partitioned)
b) exchange table2 part2 with standalone table (hash partitioned)

to achieve the original requirement

Peter, April 12, 2017 - 7:42 am UTC

perfect, this was the confirmation i searched for.
thanks for the workaround too

vilas, January 27, 2021 - 1:51 pm UTC

Hi ,

as per example , Partitions are exchange which has predefined names but during partition exchange for system generated partitions we observed following error .

ORA-02149: Specified Partition Does Not Exist

January 27, 2021 - 4:53 pm UTC

Please share example code (create table, exchange partition) showing us what you're doing

vilas, January 28, 2021 - 5:41 am UTC

CREATE TABLE MAIN_TABLE
   ( 
    TXN_ID VARCHAR2(35 BYTE), 
 REQ_MSG_ID VARCHAR2(35 BYTE), 
 TXN_LOGGED_DATE DATE DEFAULT sysdate, 
 CONSTRAINT PK_TXN PRIMARY KEY (TXN_ID, REQ_MSG_ID)
    ) 
  TABLESPACE USERS 
  PARTITION BY RANGE (TXN_LOGGED_DATE) INTERVAL (NUMTODSINTERVAL(1,'DAY')) 
 SUBPARTITION BY HASH (TXN_ID)
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION TRAN1 TABLESPACE USERS,
    SUBPARTITION TRAN2 TABLESPACE USERS,
    SUBPARTITION TRAN3 TABLESPACE USERS,
    SUBPARTITION TRAN4 TABLESPACE USERS

  )
 (
 PARTITION PT_TRAN  VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  TABLESPACE USERS 
  ) ENABLE ROW MOVEMENT;



CREATE TABLE EXCH_TABLE
   ( 
    TXN_ID VARCHAR2(35 BYTE), 
 REQ_MSG_ID VARCHAR2(35 BYTE), 
 TXN_LOGGED_DATE DATE DEFAULT sysdate, 
 CONSTRAINT PK_TEMP_MSGT PRIMARY KEY (TXN_ID, REQ_MSG_ID)
)PARTITION BY HASH (TXN_ID) PARTITIONS 4;


CREATE TABLE MAIN_TABLE_HIST 
   ( 
    TXN_ID VARCHAR2(35 BYTE), 
 REQ_MSG_ID VARCHAR2(35 BYTE), 
 TXN_LOGGED_DATE DATE DEFAULT sysdate, 
 CONSTRAINT PK_TXN_MSGQ PRIMARY KEY (TXN_ID, REQ_MSG_ID)
    ) 
  TABLESPACE USERS 
  PARTITION BY RANGE (TXN_LOGGED_DATE) INTERVAL (NUMTODSINTERVAL(1,'DAY')) 
 SUBPARTITION BY HASH (TXN_ID)
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION TRAN1 TABLESPACE USERS,
    SUBPARTITION TRAN2 TABLESPACE USERS,
    SUBPARTITION TRAN3 TABLESPACE USERS,
    SUBPARTITION TRAN4 TABLESPACE USERS

  )
 (
 PARTITION PT_TRAN  VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  TABLESPACE USERS 
  ) ENABLE ROW MOVEMENT;

 

SELECT partition_name PNAME, high_value
     FROM user_tab_partitions
    WHERE table_name = 'MAIN_TABLE';
    
PT_TRAN     TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P25760 TO_DATE(' 2021-01-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P25765 TO_DATE(' 2021-01-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P25770 TO_DATE(' 2021-01-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P25775 TO_DATE(' 2021-01-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

    
ALTER TABLE MAIN_TABLE EXCHANGE PARTITION SYS_P25760 WITH TABLE EXCH_TABLE;

ALTER TABLE MAIN_TABLE_HIST EXCHANGE PARTITION SYS_P25760 WITH TABLE EXCH_TABLE;



ORA-02149: Specified partition does not exist
02149. 00000 -  "Specified partition does not exist"
*Cause:    Partition not found for the object.
*Action:   Retry with correct partition name.

January 28, 2021 - 10:11 am UTC

That uses the same system-generated partition name for the main and hist table; these will be different! You need to find the name of the partition in main_table_hist

Exchanging interval partition

A reader, January 28, 2021 - 7:21 am UTC


Interval partitions are special. Try with "exchange partition for .. with table .."

Cheers.

Exchanging interval partition

Rajeshwaran, Jeyabal, January 29, 2021 - 6:21 am UTC

yes, you should be using " exchange partition for " syntax with interval partitioned tables.

something like this:

demo@XEPDB1> create table main_table
  2  partition by range( created )
  3  interval( numtodsinterval(1,'day') )
  4  subpartition by hash( object_id )
  5  subpartitions 4
  6  ( partition p_dec2020 values less than (
  7  to_date('01-jan-2021','dd-mon-yyyy') ) )
  8  as
  9  select owner,object_name,object_type,object_id,
 10  trunc(sysdate)+ mod(rownum,3) as created
 11  from all_objects;

Table created.

demo@XEPDB1> create table interim_table
  2  partition by hash( object_id )
  3  partitions 4
  4  for exchange with
  5  table main_table;

Table created.

demo@XEPDB1> create table main_table_HIST
  2  partition by range( created )
  3  interval( numtodsinterval(1,'day') )
  4  subpartition by hash( object_id )
  5  subpartitions 4
  6  ( partition p_dec2020 values less than (
  7  to_date('01-jan-2021','dd-mon-yyyy') ) )
  8  as
  9  select * from main_table
 10  where 1 = 0 ;

Table created.

demo@XEPDB1> alter table main_table
  2  exchange partition SYS_P3706
  3  with table INTERIM_TABLE;

Table altered.

demo@XEPDB1> select count(*) from main_table partition(sys_p3706);

  COUNT(*)
----------
         0

demo@XEPDB1> select count(*) from interim_table;

  COUNT(*)
----------
     22906

demo@XEPDB1> alter table main_table_hist
  2  exchange partition for ( to_date('29-jan-2021','dd-mon-yyyy') )
  3  with table interim_table;

Table altered.

demo@XEPDB1> select count(*) from interim_table;

  COUNT(*)
----------
         0

demo@XEPDB1> select count(*) from main_table_hist partition for (to_date('29-jan-2021','dd-mon-yyyy'));

  COUNT(*)
----------
     22906

demo@XEPDB1>

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

different scenario

Exchanging interval partition

Exchanging interval partition

More to Explore

VLDB

VLDB

Exchange range-hash partition to another range-hash partition table.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

different scenario

Exchanging interval partition

Exchanging interval partition

More to Explore

VLDB

VLDB