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.

Advertisements

One thought on “EXCHANGE PARTITION revisited

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

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