CODEWITHSUNDEEP
sqloracle-databaseoracle11g
wolφi
wolφi

Reputation: 8361

How to drop columns in a compressed table?

After compressing a table, it's not possible to drop a column any more:

CREATE TABLE foo (p NUMBER, i NUMBER, j NUMBER) COMPRESS BASIC;

ALTER TABLE foo DROP COLUMN j;
ORA-39726: unsupported add/drop column operation on compressed tables

It is possible if advance compression is used:

CREATE TABLE foo (p NUMBER, i NUMBER, j NUMBER) COMPRESS FOR OLTP;

ALTER TABLE foo DROP COLUMN j;
Table FOO altered.

However, the column is not really dropped, just hidden:

SELECT column_name, data_type, hidden_column 
  FROM user_tab_cols WHERE table_name = 'FOO';

COLUMN_NAME                 DATA_TYPE  HIDDEN_COLUMN
P                           NUMBER     NO
I                           NUMBER     NO
SYS_C00002_18030204:09:26$  NUMBER     YES

This causes an error when partitions are exchanged:

CREATE TABLE par (p NUMBER, i NUMBER) 
 PARTITION BY LIST(p)(
 PARTITION p1 VALUES(1), 
 PARTITION p2 VALUES(2)
);

ALTER TABLE par EXCHANGE PARTITION p1 WITH TABLE foo;
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

How can the hidden column be removed? I tried 

ALTER TABLE foo DROP UNUSED COLUMNS;

but it doesn't help:

SELECT column_name, data_type, hidden_column 
  FROM user_tab_cols WHERE table_name = 'BAR';

COLUMN_NAME                 DATA_TYPE  HIDDEN_COLUMN
P                           NUMBER     NO
I                           NUMBER     NO
SYS_C00002_18030204:09:26$  NUMBER     YES

Upvotes: 9

Views: 18971

Answers (3)

66_66
66_66

Reputation: 35

I had a similar kind of issue where I have compressed the table. Later when tried to drop one of the columns in that compressed table, it gave me error ORA-39726: unsupported add/drop column operation on compressed tables.

The solution that worked for me is - I have run COMPRESS FOR ALL OPERATIONS command on the table and then I ran the drop column command, it worked for me.

eg: ALTER TABLE <TABLE_NAME> COMPRESS FOR ALL OPERATIONS;

ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;

Upvotes: 2

Toolkit
Toolkit

Reputation: 11119

update foo set j = null;
commit;
alter table foo set unused column j;

Read more

Upvotes: 1

wolφi
wolφi

Reputation: 8361

Oracle support document 1987500.1 "How to Drop Columns in Compressed Tables" had the solution: the table needs to be uncompressed first, the the columns removed, then it can be compressed again:

ALTER TABLE foo MOVE NOCOMPRESS;
ALTER TABLE foo DROP UNUSED COLUMNS;
ALTER TABLE foo MOVE COMPRESS FOR OLTP;

SELECT column_name, data_type, hidden_column 
  FROM user_tab_cols WHERE table_name = 'FOO';

COLUMN_NAME                 DATA_TYPE  HIDDEN_COLUMN
P                           NUMBER     NO
I                           NUMBER     NO

ALTER TABLE par EXCHANGE PARTITION p1 WITH TABLE foo;
Table PAR altered.

N.B. The compression/recompression is fast if the table is empty.

Upvotes: 11

Related Questions