EXCHANGE PARTITION those pesky columns

Posted by

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

  1. 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.
    
    
    
    1. 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
      
    2. 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>

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

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

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

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

  6. 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!

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

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

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

      1. 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 ?

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.