Learning is not a spectator sport

January 20, 2013

EXCHANGE PARTITION those pesky columns

Filed under: Uncategorized — connormcdonald @ 7:40 am

 

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

5 Comments »

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

    Comment by Noons — January 21, 2013 @ 1:14 pm

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

    Comment by Jonathan Lewis — January 22, 2013 @ 5:43 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
      

      Comment by connormcdonald — January 22, 2013 @ 7:48 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.

        Comment by Jonathan Lewis — January 22, 2013 @ 7:57 pm

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

      Comment by Sergey — July 26, 2013 @ 11:42 am


RSS feed for comments on this post. TrackBack URI

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

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: