Continuing the theme of this post by Howard, I came across a scenario today which was resulting in this error:
ORA-14097 - column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
I ran my checker script to try and identify exactly what the mismatch was but it came back with nothing. My script, whilst useful, isn’t perfect – and indeed there was an error in it (fixed now) which led to it not identifying the problem for this scenario – but given that it couldn’t find the problem, I had to manually look at all the attributes across the tables to try and identify a difference.
For a long time I was left perplexed because my script was suggesting that everything was OK and my script checks quite a few things now – what I wasn’t taking into account was that the script was wrong and that one of the things it was supposedly checking for, it was in fact overlooking.
In the end I found that a number of columns on the source table were NULLable whilst they were NOT NULL on the target. My script was supposed to be checking for this – which is why I was struggling to fix the problem for so long. After matching the nullability on both tables in the partition, the exchange ran through fine…but I guess my point would be, that the error message above doesn’t really convey the message that the problem might be a mismatch in the optionality of a column or columns on the tables involved.
Being a fan of fictional detective, Sherlock Holmes, I should have considered his position that “It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth”…even if that truth is that your own script is at fault!
In attempting to investigate the problem I knocked up a simple script to demonstrate the problem and then the fix:
conn alpha/alpha@j4134
drop table source purge
/
create table source(pk_col1 number not null
,other_col1 number
,other_col2 date
,other_col3 varchar2(20)
)
/
create unique index test_pki on source(pk_col1)
/
alter table source add constraint test_pk primary key(pk_col1) using index
/
grant select on source to beta
/
insert into source(pk_col1,other_col1,other_col2,other_col3)
select l
, l
, trunc(sysdate+l)
, 'XXX'to_char(l)
from (select level l from dual connect by level < 1000)
/
commit
/
conn beta/beta@j4134
drop table target purge
/
create table target(pk_col1 number not null
,other_col1 number not null
,other_col2 date
,other_col3 varchar2(20)
)
partition by range(pk_col1)
(partition p1 values less than(1000)
,partition p2 values less than(2000)
)
/
create unique index test_pki on target(pk_col1) local
/
alter table target add constraint test_pk primary key(pk_col1) using index
/
Attention viagra uk sales Learn More Here Deficit Disorder (ADD, ADHD) has become a serious issue for men in the last 20 years and recover its investment. This action allows more amount of blood they can hold. order levitra on line For further information regarding precautions, do some research online and read some reviews, pickup a magazine and read up the usage instructions. cialis wholesale india buying here Getting distracted from mental peace- Today, when life has become hectic and stressed mindfulness medication should be levitra sale included as must-do activity. alter table target exchange partition p1 with table alpha.source
/
conn alpha/alpha@j4134
alter table source modify(other_col1 number not null)
/
conn beta/beta@j4134
alter table target exchange partition p1 with table alpha.source
/
…and the results…
Connected.
Table dropped.
Table created.
Index created.
Table altered.
Grant succeeded.
999 rows created.
Commit complete.
Connected.
Table dropped.
Table created.
Index created.
Table altered.
alter table target exchange partition p1 with table alpha.source
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Connected.
Table altered.
Connected.
Table altered.
Whilst in the example Howard gave, I think the issue revolved around the use of the word “shape” in error ORA-42016 and whether shape includes “data type” or not, error ORA-14097 seems to revolve around whether nullability is included under the phrase “column type” – I think both errors could do with being either slightly reworded or perhaps split out into separate errors which are more indicative of the true problem or problems at hand.
