Home » RDBMS Server » Server Administration » Error while exchanging partition (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Error while exchanging partition [message #547665] Thu, 15 March 2012 11:05 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi,

I am not able to exchange partition by following below steps:

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;



and getting the below erro:

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.


Can you please tell, why this error?

Thank you.
Re: Error while exchanging partition [message #547709 is a reply to message #547665] Thu, 15 March 2012 21:26 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
The table manu_tst_hashed_tbl_bkp have a primary,but the table manu_tst_smpl_tbl have no. you can create a primary in table manu_tst_smpl_tbl and try it.
Previous Topic: Regarding SGA Parameters
Next Topic: ORA-28500 connect error
Goto Forum:
  


Current Time: Thu Mar 28 17:03:03 CDT 2024