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
Good case! In fact only DBA with strong experience knows that or will be able to find such crazy solution. Waiting for more 🙂