Here is my partitioned table
SQL> desc PAR
Name Null? Type
----------------------------- -------- ------------
X NUMBER(38)
Y NUMBER(38)
and it has a couple of partitions
SQL> select partition_name
2 from dba_tab_partitions
3 where table_name = 'PAR';
PARTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an exchange partition operation.
SQL> create table EXCH as
2 select * from PAR
3 where 1=0;
Table created.
SQL> desc EXCH
Name Null? Type
----------------------------- -------- --------------
X NUMBER(38)
Y NUMBER(38)
So now I’m ready to go…But then this happens…
SQL> alter table PAR exchange partition P1 with table EXCH;
alter table PAR exchange partition P1 with table EXCH
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Well…that’s odd. I created the EXCH table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.
SQL> alter table PAR exchange partition P1 with table EXCH without validation;
alter table PAR exchange partition P1 with table EXCH without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Nope…still problems. So I go back and double check the columns
SQL> select column_name
2 from dba_tab_columns
3 where table_name = 'PAR';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2 from dba_tab_columns
3 where table_name = 'EXCH';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my partitioned table that is no longer readily apparent.
I dropped a column. Or more accurately, because it was a partitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS
SQL> select column_name
2 from dba_tab_cols
3 where table_name = 'PAR';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.
So am I stuck forever ? Do I have to drop the column ? Or reload the PAR table without the unused column ? All of those things don’t sound too palatable.
No. All I need do is get the columns in my template table into a similar state.
SQL> alter table EXCH add Z int;
Table altered.
SQL> alter table EXCH set unused column Z;
Table altered.
And we can try again…
SQL> alter table PAR exchange partition P1 with table EXCH;
Table altered.
Wow! Thanks for that, Connor: damn useful to know!
Connor,
You’d better hope you have the original code that created the partitioned table and marked the columns unused – your example made it look easy, but the exchange table has to have the right column types appearing in the right order before the exchange becomes legal. As far as I can see, the only way to reverse engineer the necessary information if you haven’t got it handy is to query back to sys.col$ so that you can create the table with columns in the segcol# order – here’s a sketch for a simple table where I’ve marked three columns unused:
Hi Jonathan,
I think DBA_TAB_COLS should be sufficient using INTERNAL_COLUMN_ID
Connor,
Absolutely – and it’s only been there since 9i !
How did I miss that – possibly by not making an effort to forget about dba_tab_columns.
Hi Connor,
For unsed columns DBA_TAB_COLS is enough. Unfortunately, it’s not enough for columns modified as “default not null” in 11g.
SQL> drop table t;
Table dropped
SQL> drop table tp;
Table dropped
SQL> create table t(id integer);
Table created
SQL> alter table t add c1 integer default 999 not null;
Table altered
SQL> create table tp(id integer, c1 integer default 999 not null)
2 partition by range (id)
3 (partition p1 values less than (1),
4 partition p2 values less than (2),
5 partition p3 values less than (3),
6 partition p9 values less than (maxvalue)
7 );
Table created
SQL> alter table tp exchange partition p2 with table t;
alter table tp exchange partition p2 with table t
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SQL>
SQL> select column_name, column_id, internal_column_id
2 from user_tab_cols
3 where table_name = ‘T’
4 order by internal_column_id;
COLUMN_NAME COLUMN_ID INTERNAL_COLUMN_ID
—————————— ———- ——————
ID 1 1
C1 2 2
SQL> select column_name, column_id, internal_column_id
2 from user_tab_cols
3 where table_name = ‘TP’
4 order by internal_column_id;
COLUMN_NAME COLUMN_ID INTERNAL_COLUMN_ID
—————————— ———- ——————
ID 1 1
C1 2 2
SQL> select object_id
2 from user_objects where object_name = ‘T’ and object_type = ‘TABLE’
3 ;
OBJECT_ID
———-
90077
SQL> select name, col#, segcol#, property
2 from sys.col$
3 where obj#=90077
4 order by segcol#;
NAME COL# SEGCOL# PROPERTY
—————————— ———- ———- ———-
ID 1 1 0
C1 2 2 1073741824
SQL>
SQL> select object_id
2 from user_objects where object_name = ‘TP’ and object_type = ‘TABLE’
3 ;
OBJECT_ID
———-
90078
SQL>
SQL> select name, col#, segcol#, property
2 from sys.col$
3 where obj#=90078
4 order by segcol#;
NAME COL# SEGCOL# PROPERTY
—————————— ———- ———- ———-
ID 1 1 0
C1 2 2 0
SQL>
Unquestionably consider that that you stated. Your favourite reason seemed to be on the web the simplest thing to take into accout of.
I say to you, I definitely get annoyed while other folks consider issues that they plainly don’t know about.
You managed to hit the nail upon the highest as neatly as
defined out the whole thing with no need side-effects ,
other people can take a signal. Will probably be again to get more.
Thank you
thanks Mr. connormcdonald , I was stuck with this problem for the whole day without even give a thought about this.
Hi,
Interesting post but I think we should mention the new 12.2 feature “Table Creation for Partition Exchange” – that one should actually resolve these kind of issues.
Example Ref.: https://docs.oracle.com/en/cloud/paas/exadata-express-cloud/csdbf/oracle-database-12-2-new-features.html#GUID-479CD2E4-F1B2-492E-BA55-7A69A0D0B71B
I see you don’t monetize your website, don’t waste your traffic, you can earn extra bucks every month
because you’ve got high quality content. If you want to know
how to make extra bucks, search for: Mertiso’s tips best
adsense alternative
hello how can a drop the column once it is present in dba_tab_columns ( my alter TABLE drop unset columns ) do not remove it at all….
You can “alter table … drop unused columns” but that seems excessive. If you don’t need to reclaim the space, just make them unused and forget about them 🙂
This design is wicked! You definitely know how to
keep a reader entertained. Between your wit and your videos, I was almost
moved to start my own blog (well, almost…HaHa!) Excellent job.
I really loved what you had to say, and more than that, how you presented it.
Too cool!
Hi Connor,
You have used the command “alter table EXCH add Z int;”
— Here we assume that original column name (Z) is known.
But what if we don’t know the actual column name that was made unused in the past ?
— Is there any dictionary view that shows ORIGINAL_COLUMN_NAME ?
Don’t forget that in some situations, the column that is missing is no longer the “last” column, so then you’re up for more complexity to rearrange the columns using invisible/visible trick.
Hi Connor,
You have used the command : alter table EXCH add Z int
— Here we are assuming that original column name (Z) is “known”
But what if we don’t know the original column name that was made ‘unused’ ?
— Is there any dictionary view that has ORIGINAL_COLUMN_NAME ?
Regards,
Mathavan
This is exactly why we invented CREATE TABLE FOR EXCHANGE from 12c onwards
That syntax is available only from 12.2 (not from 12.1).
Unfortunately customer is in 12.1 and they have no idea how the unused columns exist in their table (hence they don’t know the original column name).
In fact, they claim that nobody explicitly set to unused.
Are there any circumstances where Oracle automatically/implicitly creates hidden columns ?
extended statistics
default not null
function based index
We use hidden columns in lots of places