drop table manu_tst_hashed_tbl_org;

drop table manu_tst_hashed_tbl_bkp;

drop table manu_tst_smpl_tbl;

CREATE TABLE manu_tst_hashed_tbl_org
(
  nmbr  number  primary key,
  chr varchar2(20)
)
partition by hash (nmbr)
  partitions 10; -- Partitions SYS_P721 to SYS_P730

CREATE TABLE manu_tst_hashed_tbl_bkp
(
  nmbr number  primary key,
  chr varchar2(20)
)
partition by hash (nmbr)
  partitions 10; -- Partitions SYS_P731 to SYS_P740

CREATE TABLE manu_tst_smpl_tbl
(
  nmbr  number,
  chr varchar2(20)
);

insert into manu_tst_hashed_tbl_org
(select rownum rn, 'a' from dual connect by level <= 100);

commit;

select * from manu_tst_hashed_tbl_org order by nmbr;

create index idx_org_tbl on manu_tst_hashed_tbl_org(nmbr,chr) local;

create index idx_bkp_tbl on manu_tst_hashed_tbl_bkp(nmbr,chr) local;

create index idx_smpl_tbl on manu_tst_smpl_tbl(nmbr,chr);

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p771) order by 1; --55,74,81,97
--select * from user_ind_partitions where partition_name='SYS_P771';

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p772) order by 1; --28,32,52,90,92

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p773) order by 1; --5,21,24,36,44,48,51,56,65,72,77,89,93

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p774) order by 1; --3,4,16,35,37,47,49,62,64,87

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p775) order by 1; --13,27,40,50,53,61,76,79,80

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p776) order by 1; --9,12,17,19,22,39,42,43,58,66,83,95,98

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p777) order by 1; --2,8,18,20,23,33,41,68,73,78,85,100

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p778) order by 1; --1,7,14,15,25,29,38,45,57,59,60,63,71,75,82,84,96

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p779) order by 1; --6,11,30,34,46,54,88

select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p780) order by 1; --10,26,31,67,69,70,86,91,94,99

alter table manu_tst_hashed_tbl_org
  exchange partition sys_p771 
  WITH TABLE manu_tst_smpl_tbl
  INCLUDING INDEXES
  without validation
  UPDATE GLOBAL INDEXES;

select index_name, table_owner, table_name, status from user_indexes where table_name='MANU_TST_HASHED_TBL_ORG';

select index_name, table_owner, table_name, status from user_indexes where table_name='MANU_TST_SMPL_TBL';
  
select distinct nmbr, chr
from  manu_tst_hashed_tbl_org partition (sys_p771) order by 1;

select * from manu_tst_smpl_tbl;

select * from manu_tst_hashed_tbl_bkp order by 1;

select * from manu_tst_smpl_tbl;

select distinct nmbr, chr
from  manu_tst_hashed_tbl_bkp partition (SYS_P841) order by 1; --shows partition doesn't get removed on exchange

select * from user_ind_partitions where partition_name='SYS_P841';

alter table manu_tst_hashed_tbl_bkp
  exchange partition sys_p841
  with table manu_tst_smpl_tbl
  INCLUDING INDEXES
  without validation
  update global indexes;

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.