Exchange partitions for compressed table and unused columns ORA-39726, ORA-14097

This article presents problems related to ORA-39726, ORA-14097 on compressed table.

In case you want to drop a column on a compressed table (partitioned or not) you can encounter following error:

ORA-39726: unsupported add/drop column operation on compressed tables

This error can occur for following type of compression:

  • batch compression
  • EXADATA compression

Exception is OLTP compression – drop is working here. You can read about compress OLTP here – Table compression in 11G

Example for batch compression

drop table test_tbl;

create table test_tbl
( id number,
  id1 number,
  id2 number )
compress  
partition by range(id)
(partition p1 values less than(10),
 partition p2 values less than(20),
 partition p3 values less than(maxvalue));

insert into test_tbl values(5,5,5);

insert into test_tbl values(15,15,15);

insert into test_tbl values(25,25,25);

alter table test_tbl drop column id2;

You can set this column as unused

alter table test_tbl set unused column id2;

Now column is not visible but still exists in the table

desc test_tbl

Name Null Type   
---- ---- ------ 
ID        NUMBER 
ID1       NUMBER

select * from USER_UNUSED_COL_TABS
where table_name='TEST_TBL';

TABLE_NAME                          COUNT
------------------------------ ----------
TEST_TBL                                1

Setting column as unused is very nice option but you can have another problem how to exchange partition for such table.

create table test_tbl_tmp
as select * from test_tbl
where rownum < 0;

alter table test_tbl
exchange partition p1 with table test_tbl_tmp
including indexes without validation;

ORA-14097: column type or size mismatch 
           in ALTER TABLE EXCHANGE PARTITION

There are two options:

  • total rebuild of your partitioned table – CREATE TABLE AS SELECT, dbms_redefinition
  • create temp table with unused column
--example for option 2
create table test_tbl_tmp
( id number,
  id1 number,
  id2 number );

alter table test_tbl_tmp set unused column id2;  

alter table test_tbl
exchange partition p1 with table test_tbl_tmp
including indexes without validation;

Option 2 still seems to creazy but possible 🙂

Have a fun 🙂

Tomasz

 

 

 

 

 

 

 

 

One thought on “Exchange partitions for compressed table and unused columns ORA-39726, ORA-14097

  1. Good case! In fact only DBA with strong experience knows that or will be able to find such crazy solution. Waiting for more 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

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