Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> dropping column from a huge table, ORA-39726: unsupported add/drop column operation on compressed tables
sishyadba
post Aug 9 2012, 10:43 AM
Post #1


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



Hi

I am trying to drop a column from a huge partitioned table (noncompressed including partitions).
I am working on 11gR2 database for information.

i used below approach
1. alter table <tab_name> set t1 unused column;

2. alter table <tab_name> drop unused columns;

then i got the below error message

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

It seems to be strange to me since this is the work around suggested by Oracle Guru's Burleson and Tom in their articles.

Since first statement did work i could be able to add another column with the same name but still don't want the unused column on the table.

Can anyone of you suggest me what would be the reason for this error?

Sri
Go to the top of the page
 
+Quote Post
SteveC
post Aug 9 2012, 02:20 PM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



It is well documented at My Oracle Support.
Go to the top of the page
 
+Quote Post
sishyadba
post Aug 9 2012, 02:52 PM
Post #3


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



Thanks a lot for your reply Steve. Since am a developer i don't have the SR login credentials. I would really appreciate if anyone could copy paste the content of the article.

Thanks again

Sri
Go to the top of the page
 
+Quote Post
sishyadba
post Aug 9 2012, 02:58 PM
Post #4


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



anyway found the solution ..since 11g supports the advanced compression ("compress for all operations")

i did

alter table <tab_name> compress for all operations;

then i could be able to drop the unused columns using below command, Hats-off to the 11g improvements

alter table <tab_name> drop unused columns;

Thanks again for everyone who tried to help.

Sri

Go to the top of the page
 
+Quote Post
SteveC
post Aug 9 2012, 03:27 PM
Post #5


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Posting MOS content violates TOS with them.
Go to the top of the page
 
+Quote Post
burleson
post Aug 10 2012, 11:14 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,507
Joined: 26-January 04
Member No.: 13



Hi Sri,

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

See here: http://www.dba-oracle.com/t_ora_39726_tips.htm


Glad you got it working . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd September 2014 - 07:14 PM