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.

Advertisements

9 thoughts on “EXCHANGE PARTITION those pesky columns

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

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s