EXCHANGE PARTITION those pesky columns

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.

21 comments

Noons says:

January 21, 2013 at 1:14 pm

Wow! Thanks for that, Connor: damn useful to know!

Reply
Jonathan Lewis says:

January 22, 2013 at 5:43 pm
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:
```
SQL> select column_name , column_id, data_type from user_tab_cols where table_name = 'T2' order by column_id;

COLUMN_NAME                       COLUMN_ID DATA_TYPE
-------------------------------- ---------- --------------------------------
NVCUSTATUS                                1 VARCHAR2
FREQ_COLUMN                               2 NUMBER
SYS_C00005_13012209:27:08$                  NUMBER
SYS_C00002_13012209:30:06$                  NUMBER
SYS_C00004_13012209:29:15$                  NUMBER

5 rows selected.

SQL> select object_id from user_objects where object_name = 'T2';

 OBJECT_ID
----------
     78997

1 row selected.

SQL> select col#, segcol#, name from col$ where obj# = 78997;

      COL#    SEGCOL# NAME
---------- ---------- ------------------------------
         1          1 NVCUSTATUS
         0          2 SYS_C00002_13012209:30:06$
         2          3 FREQ_COLUMN
         0          4 SYS_C00004_13012209:29:15$
         0          5 SYS_C00005_13012209:27:08$

5 rows selected.
```
Reply
1. connormcdonald says:
  
  January 22, 2013 at 7:48 pm
  Hi Jonathan,
  
  I think DBA_TAB_COLS should be sufficient using INTERNAL_COLUMN_ID
```
SQL> create table T
  2   ( x1 int,
  3     x2 int,
  4     x3 int,
  5     x4 int,
  6     x5 int);

Table created.

SQL> alter table T set unused column x4;

Table altered.

SQL> alter table T set unused column x2;

Table altered.

SQL> select column_name,  column_id, internal_column_id
  2  from   dba_tab_cols
  3  where  table_name = 'T'
  4  order by internal_column_id
  5  /

COLUMN_NAME                     COLUMN_ID INTERNAL_COLUMN_ID
------------------------------ ---------- ------------------
X1                                      1                  1
SYS_C00002_13012219:47:04$                                 2
X3                                      2                  3
SYS_C00004_13012219:47:04$                                 4
X5                                      3                  5
```
  Reply
  1. Jonathan Lewis says:
    
    January 22, 2013 at 7:57 pm
    
    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.
    
    Reply
2. Sergey says:
  
  July 26, 2013 at 11:42 am
  
  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>
  
  Reply
Pingback: EXCHANGE PARTITION revisited | Learning is not a spectator sport
Pingback: What you "liked" last year… | Learning is not a spectator sport
best wifi router says:

January 18, 2017 at 10:28 pm

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

Reply
MOHIT TINNA says:

May 5, 2017 at 9:53 pm

thanks Mr. connormcdonald , I was stuck with this problem for the whole day without even give a thought about this.

Reply
rparvu says:

July 28, 2017 at 4:14 pm

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

Reply
Pingback: 2017–what grabbed your attention | Learning is not a spectator sport
LastBernadine says:

January 18, 2018 at 1:16 pm

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

Reply
serge fofana says:

October 19, 2018 at 3:53 pm

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….

Reply
1. Connor McDonald says:
  
  November 2, 2018 at 8:47 am
  
  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 🙂
  
  Reply
Elane says:

February 1, 2020 at 11:45 pm

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!

Reply
Mathavan says:

November 16, 2022 at 8:18 pm

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 ?

Reply
1. Connor McDonald says:
  
  November 16, 2022 at 8:28 pm
  
  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.
  
  Reply
mathavan says:

November 16, 2022 at 8:23 pm

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

Reply
1. Connor McDonald says:
  
  November 23, 2022 at 10:40 am
  
  This is exactly why we invented CREATE TABLE FOR EXCHANGE from 12c onwards
  
  Reply
  1. Mathavan says:
    
    November 23, 2022 at 6:44 pm
    
    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 ?
    
    Reply
    1. Connor McDonald says:
      
      November 23, 2022 at 7:03 pm
      
      extended statistics
      default not null
      function based index
      
      We use hidden columns in lots of places
      
      Reply