EXCHANGE PARTITION revisited

A while back I did a blog post showing that when you have set a column to UNUSED, it still “counts” as a validation step when doing exchange partition. So if you had a partitioned table that previously had a column set to unused, then the candidate table to be exchanged in also had to have undergone the alteration. Just having the visible columns aligned is not sufficient.

In the comments, Sergey presented an interesting variation of this, which we will look at below


SQL> drop table t purge;

Table dropped.

SQL> drop table tp purge;

Table dropped.

SQL>
SQL> create table t(id integer, c1 integer default 999 not null);

Table created.

SQL>
SQL> create table tp(id integer)
  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>
SQL> alter table tp add c1 integer default 999 not null;

Table altered.

So in our partitioned table, at some stage we added a new column C1 and its a non-null column. This in itself is pretty cool, in that in recent releases, this is an instant operation – the historical rows do not need to be revisited and updated.

Our two tables look identical, but let’s see what happens when we do an exchange:



SQL>
SQL> set lines 60
SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER(38)
 C1                            NOT NULL NUMBER(38)

SQL> desc TP
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER(38)
 C1                            NOT NULL NUMBER(38)


SQL>
SQL> alter table tp exchange partition p2 with table t;
alter table tp exchange partition p2 with table t
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

We get a similar issue. Because one table had the default column definition from creation, the other via an alteration, the tables are still seen as “different”. If we dive into the data dictionary internals, even without really knowing the particulars, we can see a difference in the column definitions


SQL> set lines 120
SQL>
SQL> select obj#, name, col#, segcol#, property
  2  from sys.col$
  3  where obj# in (
  4    select object_id
  5    from user_objects
  6    where object_name in ('T','TP')
  7    and object_type = 'TABLE'
  8  )
  9  order by obj#, segcol#;

      OBJ# NAME                                           COL#    SEGCOL#   PROPERTY
---------- ---------------------------------------- ---------- ---------- ----------
    115959 ID                                                1          1          0
    115959 C1                                                2          2          0
    115960 ID                                                1          1          0
    115960 C1                                                2          2 1073741824

4 rows selected.

So how do we solve it ? There is an event you can set so that your template table for exchange will be mapped nicely to the partitioned table. So we just set that event and use the partition table as a source


SQL>
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> alter session set events='14529 trace name context forever';

Session altered.

SQL> create table T as select * from TP where 1=0;

Table created.

SQL> alter session set events='14529 trace name context off';

Session altered.

SQL>
SQL> alter table tp exchange partition p2 with table t;

Table altered.

SQL>

As you can see, you only need the event for the creation of your table. After that, its business as usual.

One response to “EXCHANGE PARTITION revisited”

Peter kristof

March 31, 2016 at 7:58 pm

Hello, but how to solve the issue if attribute is added to the partitioning table and later is marked as unused.
Work table t can’t be created as CREATE TABLE t AS SELECT * from tp because atribute c1 is already unused in the table tp.
And unused attribute c1 in tp can have 2 different values of attribute PROPERTY in sys.col$ table.
It depends whether
attribute c1 was created by CREATE TABLE script
or
attribute c1 was created by ALTER TABLE ADD COLUMN script

And I don’t know to decide according PROPERTY values in sys.col$ table how was unused column c1 in tp table created !!!

And that’s why I can’t do exchange partition because it gives error.

Do you know how to simple solve this issue ?
DROP COLUMN of unused attribute can’t be used !

Got some thoughts? Leave a comment Cancel reply

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

Learning is not a spectator sport