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.

About these ads